Transaction Count After Execute Indicates a Mismatching Number of Begin and Commit Statements. Previous Count = 1, Current Count = 0

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

If you have a TRY/CATCH block then the likely cause is that you are catching a transaction abort exception and continue. In the CATCH block you must always check the XACT_STATE() and handle appropriate aborted and uncommitable (doomed) transactions. If your caller starts a transaction and the calee hits, say, a deadlock (which aborted the transaction), how is the callee going to communicate to the caller that the transaction was aborted and it should not continue with 'business as usual'? The only feasible way is to re-raise an exception, forcing the caller to handle the situation. If you silently swallow an aborted transaction and the caller continues assuming is still in the original transaction, only mayhem can ensure (and the error you get is the way the engine tries to protect itself).

I recommend you go over Exception handling and nested transactions which shows a pattern that can be used with nested transactions and exceptions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch
end
go

Rollback done - yet this error : Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

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.

Error Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

You need a pair of BEGIN and END for your IF statement:

UPDATE SavAccount SET Balance = Balance - 500
WHERE CustomerID = @CustID
IF ( (SELECT Balance FROM SavAccount WHERE CustomerID = @CustID) < 0)
BEGIN
    ROLLBACK TRAN
    RETURN
END

As written, only the ROLLBACK TRAN is part of the IF statement, so if it fails the IF check, it doesn't rollback the transaction, but immediately executes RETURN.

---

Related Topics