0

I am getting this error in stored procedure.

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

I read through some of the answers and found that if I return without a commit, I get the error. My stored procedure is something like this:

BEGIN TRY BEGIN if @id is null BEGIN set @id= (SELECT last_sequence_value FROM table_name WHERE sequence_name = 'id') + 1 BEGIN BEGIN TRANSACTION -- update SQL statement here IF @@ROWCOUNT = 0 BEGIN ROLLBACK TRANSACTION RAISERROR('There was an error getting unique id in the table.',10,1) RETURN END IF (@@ERROR <> 0) BEGIN ROLLBACK TRANSACTION RAISERROR('There was an error updating record to the table',10,1) RETURN END COMMIT TRANSACTION END END else BEGIN -- some sql select statements END END END TRY BEGIN CATCH -- Raise an error with the details of the exception RAISERROR(@ErrMsg, @ErrSeverity, 1) WITH SETERROR END CATCH

From the above code, I am doing a rollback and returning from the stored procedure. Yet when I run this in the perf testing environment, I get the error mentioned earlier.

Kindly help regarding this.

Improve this question
4
  • wht you want with above ? Commented Oct 17, 2017 at 4:32
  • Yes - you shouldn't use rollback transaction when nesting transactions - this rolls the outermost transaction back hence the error about @@trancount leaving as 0. You might need to use Save Points here and here Commented Oct 17, 2017 at 4:54
  • @StuartLC - yeah you are right but since I have rolled back the transaction and mentioned return statement, there is no way it should go into commit transaction. So I am still not sure why that error comes up. If I did a return without rollback then its very risky since it keeps the transaction open. Commented Oct 17, 2017 at 6:08
  • Not quite - if your proc is called from another proc (or any sql code / ado etc) which has itself already started a transaction, your PROC is NOT ALLOWED to ROLLBACK TRANSACTION; since this attempts to undo the caller's transaction. Instead, you must detect whether there is an outer transaction or not, and if there is one, to start a savepoint and rollback to your savepoint instead. It gets messy very quickly. The other option is not to start an inner transaction if there's already an outer transaction, and then just THROW if the proc fails, leaving it to the outer transaction to rollback. Commented Oct 17, 2017 at 6:16

4 Answers 4

Reset to default
4

I have fixed it by adding 

IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

in the catch block before I raise an error.

Thanks everyone for the wonderful inputs.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

I had a user report this same error in code I inherited, but it was caused by a combination of using a transaction and try/catch inside of the sproc along with calling the sproc inside of a VB.Net System.Transactions.TransactionScope block as well. If an error cropped up inside the sproc, the sproc rolled back everything there correctly, but it apparently rolled back the VB.Net transaction as well and created the mismatch. My fix was to remove the transaction code and the the try/catch blocks from the sproc since the VB.Net transaction will rollback all the inserts/updates inside the sproc if the sproc failed or if the other processing done inside the VB.Net transaction block failed as well. Hopefully this will save someone else a day of frustration.

Improve this answer

Comments

-1

Here you should use try catch blog inside begin transaction. like this

BEGIN if @id is null BEGIN set @id= (SELECT last_sequence_value FROM table_name WHERE sequence_name = 'id') + 1 BEGIN BEGIN TRANSACTION BEGIN TRY -- update SQL statement here IF @@ROWCOUNT = 0 BEGIN ROLLBACK TRANSACTION RAISERROR('There was an error getting unique id in the table.',10,1) RETURN END IF (@@ERROR <> 0) BEGIN ROLLBACK TRANSACTION RAISERROR('There was an error updating record to the table',10,1) RETURN END END TRY BEGIN CATCH -- Raise an error with the details of the exception RAISERROR(@ErrMsg, @ErrSeverity, 1) WITH SETERROR END CATCH COMMIT TRANSACTION END END else BEGIN -- some sql select statements END END
Improve this answer

1 Comment

How does it matter whether I use try catch inside or outside ? Can you please explain. I want to be sure that COMMIT TRANSACTION doesn't happen after return statement only
-1

First, compare

DECLARE @ErrorVar INT RAISERROR(N'Message', 16, 1); IF @@ROWCOUNT <> 0 PRINT 'Rows <> 0' IF @@ERROR <> 0 PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)); GO

And this

DECLARE @ErrorVar INT, @Error INT, @Cnt INT RAISERROR(N'Message', 16, 1); SELECT @ERROR = @@ERROR, @Cnt = @@ROWCOUNT IF @Cnt <> 0 PRINT 'Rows 0' IF @ERROR <> 0 PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)); GO

https://learn.microsoft.com/en-us/sql/t-sql/functions/error-transact-sql Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement being verified, or save it to a local variable that can be checked later. +

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.