Programmers learn & share
0 votes
43 views

Problem :

My Stored Procedure is giving me following error
transaction count after execute indicates a mismatching number of begin and commit statements
by (6.9k points)   | 43 views

1 Answer

0 votes

Solution :

This usually happens when one transaction is started and either it is not committed or it is not rolledback.

If the error comes in your stored procedure then it will lock the database tables as the transaction is not completed due to some runtime errors occuring in the absence of exception handling. So to avoid getting such errors you can use the Exception handling as shown below. 

SET XACT_ABORT ON
SET NoCount ON
Begin Try 
     BEGIN TRANSACTION 
        //Insert ,update queries    
     COMMIT
End Try 
Begin Catch 
     ROLLBACK
End Catch

Further Readings :

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

by (36.1k points)  
2,204 questions
2,604 answers
59 comments
241 users