Commit and Rollback - 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)

Commit and Rollback

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-23 : 22:21:48

How to make use of Commit and Rollback Transaction in stored procedurer ?

Can anyone show me a simple ?

thanks.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-03-23 : 23:25:02

Create procedure EmpInsert (@Eno int, @Ename Varchar(50), @Dept int)
AS
Begin
begin tran
begin try
--Do DML operations here
--Example
Insert into Emp Values(@Eno,@Ename,@Dept)
Commit tran
end try
begin catch
Rollback tran
--Return error message if you wish
end catch
end

Regards,
Bohra

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-03-23 : 23:28:49

Other method (Generally followed in SQL 2000)

Create procedure EmpInsert(@Eno int, @Ename Varchar(50), @Dept int)
AS
Begin
begin tran
--Do DML Operation here
Insert into Emp Values(@Eno,@Ename,@Dept)
if @@Error<>0
Rollback tran
else
Commit tran

end

Kristen
Test

22859 Posts

Posted - 2010-03-24 : 03:58:58

"if @@Error <> 0
Commit tran
else
Rollback tran
end"

Hehehe ... you're just checking if anyone is reading this aren't you?

There is a slightly more complicated scenario where if you do a ROLLBACK in an Sproc, and that Sproc is called from another SProc, which itself uses a transaction (implicit or otherwise) then it will be at the wrong transaction level / count (i.e. value of @@TRANCOUNT), and that will cause SQL to raise an error.

So ... the workaround for that is:


BEGIN TRANSACTION MyLabel_01
SAVE  TRANSACTION MyLabel_02

... some code ...

IF ... test for No Errors ...
BEGIN
	COMMIT TRANSACTION MyLabel_01
END
ELSE
BEGIN
	ROLLBACK TRANSACTION MyLabel_02
	COMMIT TRANSACTION MyLabel_01
END

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-03-24 : 06:22:37

quote:
Originally posted by Kristen

"if @@Error <> 0
Commit tran
else
Rollback tran
end"

Typo error...

Good catch..

Thanks for correcting

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources