rollback transaction - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

rollback transaction

Author

Topic

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-06-03 : 01:59:47

hi,

i have two tables one is master table another one is detail table

my questions is that once i commit the master transactions can i rollback that transaction
USE [smartsystem]
GO
/****** Object: StoredProcedure [dbo].[procstdinstextmaster] Script Date: 06/03/2010 11:21:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[procstdinstextmaster]
(
-- insert for createing instrumnet reference value for the external mictro meter
@fldrangecode bigint,
@fldinsttype bigint

)
as
set nocount on
declare @autonum bigint
begin transaction

insert into Mst_stdextrefmaster(fldrangecode,fldinsttype)
values
(@fldrangecode,@fldinsttype)
--select @autonum = max(fldexrefno) from mst_stdextrefmaster
--select @autonum
if @@error <> 0 goto ERR_HANDLER
commit transaction---- i commited
Select '0' as Result
return 0
--Exception Handing
ERR_HANDLER:
print 'unexpected error occurred!'
Rollback transaction
Select '1' as Result
return 1

Desikankannan

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-06-03 : 07:26:19

The simple answer to your question is no, but looking at your code, that is not what you meant to ask. If there is an error during the insert, it will not hit the commit anyway, it will go straight to the error and rollback.

If you are using SQL 2005, you may want to change your code to a TRY CATCH block instead as it makes it easier to read and write.

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-06-04 : 01:55:31

Hi,

can send sample code to use try and catch in sql

quote:
Originally posted by RickD

The simple answer to your question is no, but looking at your code, that is not what you meant to ask. If there is an error during the insert, it will not hit the commit anyway, it will go straight to the error and rollback.

If you are using SQL 2005, you may want to change your code to a TRY CATCH block instead as it makes it easier to read and write.

Desikankannan

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-06-04 : 04:13:35

There are some good examples in Books Online (SQL's help file), it is simple enough:

BEGIN TRY
... Do processes here.
END TRY

BEGIN CATCH
.. Do error stuff here
END CATCH

Kristen
Test

22859 Posts

Posted - 2010-06-04 : 05:24:32

You can do a COMMIT / ROLLBACK using a SAVEPOINT - so that you can rollback part of a transaction

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources