1

I have a stored procedure that should return the below RAISERROR message when called. However, the text is appended to the message when returned:

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

Why is this? Is the statement order in the CATCH block correct?

SET XACT_ABORT ON; -- ensures the entire transaction is terminated and rolled back. **https://learn.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql

BEGIN TRY
BEGIN TRAN --all as one transaction.

    IF EXISTS(
            SELECT 
                SupervisorID
            FROM [dbo].[UserDetail]
            WHERE
                SupervisorID IS NOT NULL
            )
    BEGIN 
        RAISERROR('Unable to alter a approved data.', 18, 1)    
    END
COMMIT TRAN
END TRY
BEGIN CATCH
    -- Determine if an error occurred.  
    IF @@TRANCOUNT > 0  
        ROLLBACK;

    -- Return the error information. 
    DECLARE 
        @error_number AS INT,
        @error_message AS NVARCHAR(1000),
        @error_severity AS INT;
    SELECT
        @error_number=ERROR_NUMBER(),
        @error_message=ERROR_MESSAGE(),
        @error_severity=ERROR_SEVERITY();
    RAISERROR(@error_message,@error_severity,1);

END CATCH
1
  • If I run the exact code above (albeit changing your IF EXISTS to an IF 1 <> 0), it works and raises the correct error. Is there something you're not showing us? Commented May 4, 2017 at 15:01

1 Answer 1

1

You can only get the "Transaction count mismatch" error if you have an open transaction prior to enter this stored procedure. The problem is that BEGIN TRAN will increment @@TRANCOUNT by 1 each time it is called, and COMMIT will decrement by 1 each time it is called, yet ROLLBACK will always reset back to 0 since there truly is no such thing as a nested transaction in SQL Server.

In your case, you have an active transaction before this stored procedure is called, but the ROLLBACK in the CATCH block resets @@TRANCOUNT back to 0, hence the error.

The trick to handling nested stored procedures, each having their own error handling, is to not call BEGIN TRAN if you are already in an active transaction (it doesn't do anything anyway outside of open you up to the possibility of getting this error ;-). The template that I prefer using is found in the following answer of mine, also here on DBA.SE:

Are we required to handle Transaction in C# Code as well as in stored procedure

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.