Adding transaction to stored procedures

I really need your help with transaction in the following stored procedure.

when I try the transaction I have the following error.

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

Thank you!

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke5y ago
Okay. In that case try moving your 'commit tran' from line number 55 to 50.

Before leaving the stored procedure (in other words executing a return statement) you should either commit or rollback the transaction that you started with begin statement.

Hope it helps!
0
Schleid Alex5y ago
No I have not used transction in the called stored procedures. Only in the one posted here
0
Nitin Sontakke5y ago
Hello,

Have you used Begin Tran / Commit Tran / Rollback Tran in ANY of the stored procedures you are calling in this stored procedure?

If yes, their count should match. It is just like curly { } braces that we use in C# language. Each should have a closing one.

Similarly the count of Begin Tran should be = Commit Tran / Rollback Tran statements.

Every Begin Tran increments the count and Commit Tran and Rollback Tran decrements it.

If you want to debug this, you can insert print / select statements in between your procedures and use @@trancount built in variable.

Hope it helps!
0