CODEWITHSUNDEEP
sql-serverstored-procedurestransactionstry-catch
hwilson1
hwilson1

Reputation: 499

TSQL - Error when running a procedure within a transaction where the procedure includes a transaction

Within a stored procedure I have a try catch block with a transaction which will commit if control never passes to the catch otherwise will rollback if there's an error and control does pass to the catch block.

I'm running this stored procedure from within a transaction, as per the below example:

 CREATE PROCEDURE sp_test
 AS
 BEGIN
     BEGIN TRY
         BEGIN TRAN
             DECLARE @var INT = 1
             IF @var <> 2 
                 BEGIN
                     RAISERROR('error', 16,1)
                 END
         COMMIT
     END TRY
     BEGIN CATCH
         ROLLBACK
         PRINT 'Rolled back'
     END CATCH
 END


 BEGIN TRAN

 EXEC sp_test

When I run this, I'm seeing the error

Msg 266, Level 16, State 2, Procedure sp_test, Line 0 [Batch Start Line 7]
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

But I don't understand why.

I'm starting a transaction outside of the stored procedure. I'm then going into the SP and beginning another transaction. Once control passes to the catch block, both transactions should be rolled back so transaction count should be 0.

Obviously there's a gap in my understanding here.

Upvotes: 0

Views: 969

Answers (1)

PeterHe
PeterHe

Reputation: 2766

This behaviour is by design. Whenever a transaction is started, the @@TRANCOUNT session variable will increase one, and when a transaction is committed, it decreases one. However, when a transaction rolls back, it rolls back the all nested transactions. So in your case, the rollback in your catch will roll back the 2 nested transactions and @@TRANCOUNT will become 0. This will cause the caller throw the mismatch transaction count exception.

To avoid this issue, you can check @@TRANCOUNT in your SP and only start a new transaction when it is 0 and set a flag (a local variable) to indicate that, e.g. @new_tarn=1. Then you commit or rollback only when @new_tran=1.

Upvotes: 2

Related Questions