Sql-server – Stored Procedure RAISERROR – Transaction Count Msg Returned

error handlingsql serverstored-procedurestransaction

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://docs.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

Best Answer

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

Related Question