Why are these nested SQL Server transactions throwing a mismatch error if there is a rollback?

Question

By running 'Test Errors' I get unexpected results. I thought by checking for @@Trancount it would avoid mismatches. Can anyone help me with a better way to rollback errors? I want to rollback all transactions which are nested. Stored procedures can be both nested and on their own.

alter procedure TestErrors
as
begin
    begin try
        begin transaction

        exec TestErrorsInner;

        IF @@TRANCOUNT > 0
            commit transaction;
    end try
    begin catch
        IF @@TRANCOUNT > 0
            rollback transaction;
        select ERROR_MESSAGE();
    end catch
end

alter procedure TestErrorsInner
as
begin
    begin try
        begin transaction

        RAISERROR('Test Error',16,1);

        IF @@TRANCOUNT > 0
            commit transaction;
    end try
    begin catch
        IF @@TRANCOUNT > 0
            rollback transaction;
        select ERROR_MESSAGE();
    end catch
end

Results:

Test Error

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

Answer 1

This is because you are catching a transaction in the TestErrors which is not in Active state.

You have already rolled back your transaction in Catch block of TestErrorsInner. Then again you are trying to do COMMIT/ROLLBACK it in TestErrors. So it is throwing an error.

It is your responsibility to Raise an Error explicitly again in Catch block of TestErrorsInner. So that Error will be the input for Parent SP.

So your TestErrorsInner should be like

ALTER PROCEDURE TESTERRORSINNER
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION

        RAISERROR('TEST ERROR',16,1);

        IF @@TRANCOUNT > 0
            COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        --SELECT ERROR_MESSAGE();
         RAISERROR('TEST ERROR in Catch',16,1);  --Here Raised 
    END CATCH
END

Now execute the TestErrors Stored procedure, you won't get that error.

And You can check the Transaction Status with XACT_STATE()

Calling XACT_STATE() will give result of 0 or 1 or -1 (From MSDN)

• If 1, the transaction is committable.
• If -1, the transaction is uncommittable and should be rolled back.
• if XACT_STATE = 0 means there is no transaction and a commit or rollback operation would generate an error.