Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing. Previous count = %ld, Current count = %ld

Such an error has appeared these days to me in SQL using SPROC in another SPROC where these are using transactions:


Msg 266, Level 16, State 2, Procedure SPROC_NAME2, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.

So what is that???
I will explain.


Let assume that I have 2 Stored Procedures:

CREATE PROCEDURE proc1
AS
BEGIN TRANSACTION 
...
EXEC proc2
COMMIT TRANSACTION

-- and the second Stored Procedure:

CREATE PROCEDURE proc2
AS
BEGIN TRANSACTION 
...
ROLLBACK/COMMIT TRANSACTION -- depending on a condition


So what will be the results?

1) Everything is cool when COMMIT is executed in the second Stored Procedure - proc2
2) ERROR with message: 266: "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2." when ROLLBACK is executed in the second Stored Procedure.

WHY??


Because the transaction count is incremented every time you execute a "BEGIN TRANSACTION", and it is decremented every time you execute a "COMMIT TRANSACTION" and it doesn't decrement anything when "ROLLBACK TRANSACTION" is executed.

So what is the solution for this case?

You need to add "COMMIT TRANSACTION" after each "ROLLBACK TRANSACTION", so that it decrements the transaction count indicator correctly. I know this is nonsense but this is the only way to get rid of the "Msg 266, Level 16, State 2, Procedure SPROC_NAME2, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2." error.

Please share this article.
Thanks.