how to use transactions in this scenario

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 2000 Forums

Transact-SQL (2000)

how to use transactions in this scenario

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-08-16 : 16:51:18

Hi friends
i've a stored proc that calls itself recursively and updates some tables.
what i want is if any of these updates fails in error i want to rollback all changes.
i know i can use BEGIN TRANS and END TRANS ,how can i use it in this case as same procedure can be called any no of times within same proc

my code will look something like below script
http://www.sqlteam.com/item.asp?ItemID=8595

Thanks for ur advise

Cheers

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-08-16 : 21:15:03

Do your transaction start/commit/rollback outside of the function and call away returning the outcome you want. You could also issue the rollback in the function I suppose.

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-08-16 : 22:38:08

thats a good idea .will give that a try
Thanks

Cheers

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-16 : 22:47:19

You can have the procedure check the value of @@NESTLEVEL, and only start the tranaction when it is zero and commit or rollback when it is zero.

CODO ERGO SUM

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-08-16 : 22:54:06

ok i did not know that one.

Cheers

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-16 : 23:09:58

You should also check the value of @@TRANCOUNT to make sure there is not already a transaction open before you begin a transaction.

I check @@TRANCOUNT is all stored procedures. Unless I specifically design a proc to be executed inside a transaction, I treat a transaction that is open when a proc is executed as an error and perform a rollback. This helps to eliminate blocking in applications that do a poor job of transaction and error handling.

CODO ERGO SUM

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-08-16 : 23:14:38

Thanks Michael.
can you give me some sample so that i understand concept better.
Thanks

Cheers

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-17 : 00:18:17

You could look at the examples here to start:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_2nxo.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_8lx0.asp

CODO ERGO SUM

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-08-17 : 00:25:06

Thank you very much . much appreciated.

Cheers

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-08-17 : 04:15:57

Interesting MVJ - I take commpletely the opposite approach! I always assume a procedure *must* be taking part in a transaction - why would it not be? If there was a "must be in a transaction" mode I'd turn it on for sure and might consider your @@TRANCOUNT idea in the future albeit for completely the reverse situation! Where possible I always do the transaction out of the procedure and in the client otherwise you end up with the same "do I really do my begin trans/commit here" problems just by calling nested stored procedures. It seriously limits how you can string them altogether if you end up committing all over the place.
Someone, somewhere knows where the real transaction begins & ends and it's rarely the individual stored procedures themselves (unless it's a "top level" one). Even then that can change as requirements do.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources