TSQL doesn't handle multiple transactions very well. In fact it doesn't handle it at all. So what do you do when you have nested stored procedures, all which require transaction handling?
TSQL has a built in function that will return the number of BEGIN TRANSACTION statements that have been called on the current connection.
@@TRANCOUNT
Using this you can choose whether to begin a transaction or not based on the value it returns. If it returns a number greater than 0 then you are currently in a transaction.
However if it returns a 0 then there were no other transactions and you are free to start one. The best way to do this is to set a variable that indicates if a transaction is needed, since when you start your new transaction and you try to check again when you need to do a rollback or commit, @@TRANCOUNT will not return 0 since you recently started a transaction.
You can easily do it like this:
IF (@@TRANCOUNT = 0)
SET @transReq = 1
ELSE
SET @transReq = 0
IF (@transReq = 1)
BEGIN TRANSACTION
--Insert Code Here
--If no error
IF (@transReq = 1)
COMMIT TRANSACTION
--else
IF (@transReq = 1)
ROLLBACK
With this you can have multiple nested stored procedures and you won't have to worry about nested transactions errors.
By: Dustin Yee, Application Developer, WebSan Solutions Inc. a Microsoft Dynamics GP Partner and 2013 Canadian Channel Elite Awards Finalist.