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

Question

When any error occur in the following proc then error message is "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"

CREATE PROCEDURE [dbo].[proc_PurchaseOrder_Create](@CustomerID INT, @CustomerOrderID INT OUTPUT)          
AS          
SET NOCOUNT ON;          
--------------------------------          
DECLARE @Return_Message VARCHAR(1024)          
DECLARE @ErrorCode  INT            
DECLARE @ErrorStep  VARCHAR(200)          
DECLARE @UserID INT  
--------------------------------          
DECLARE @CustomerCartID INT,@PONumber VARCHAR(255), @CartTotalAmount NUMERIC          

BEGIN TRY          

    BEGIN TRAN                  
  -----------------------------------------------------------------------------             
  SELECT @ErrorCode = @@ERROR          
      SET NOCOUNT ON;      
  IF NOT EXISTS(SELECT CustomerCartID FROM TxnCustomerCart WHERE CustomerID = @CustomerID)           
     BEGIN          
        SET @Return_Message= 'No Cart item is available'          
           SET @ErrorCode = 1          
        END                  
        ELSE          
        BEGIN          
    -----------------------------------------------------------------------------          
    SELECT @UserID = ISNULL(UserID,0) FROM TxnCustomers WHERE CustomerID = @CustomerID      
    -----------------------------------------------------------------------------          
    SELECT @CartTotalAmount = CartTotalAmount, @CustomerCartID = CustomerCartID FROM TxnCustomerCart WHERE @CustomerID = CustomerID            
    SELECT @ErrorStep = 'Error on inserting data into TxnCustomerOrders';            
    INSERT INTO TxnCustomerOrders(CustomerID,OrderDate,OrderStatus,Ramarks,PaymentCategoryCode,CreatedBy,CreatedDatetime)          
    VALUES(@CustomerID,GETDATE(),'PRTRCVD','','Wallet',@UserID,GETDATE())            
    SET @CustomerOrderID = SCOPE_IDENTITY();          

    SELECT @ErrorStep = 'Error on generating PONumber';            
    SET @PONumber =dbo.fun_getPONumber(@CustomerID, @CustomerOrderID)          

    SELECT @ErrorStep = 'Error on inserting data into TxnPurchaseOrder';          

    INSERT INTO TxnPurchaseOrder (CustomerOrderID, PONumber, PODate, POAmount, PODocID, PODocPath, CreatedBy, CreatedDate)          
     VALUES (@CustomerOrderID, @PONumber, GETDATE(), @CartTotalAmount, REPLACE(@PONumber, '/' , '-'), REPLACE(@PONumber, '/' , '-') + '.pdf', @UserID, GETDATE())              

    SELECT  @ErrorCode  = 0, @Return_Message = 'Purchase order successfully created'          
  END       

  SET NOCOUNT OFF;         
    COMMIT TRAN               
    RETURN @ErrorCode -- =0 if success,  <>0 if failure          
END TRY           
BEGIN CATCH          
    IF @@TRANCOUNT > 0 ROLLBACK          

    SELECT @ErrorCode = ERROR_NUMBER()          
        , @Return_Message = @ErrorStep + ' '          
        + cast(ERROR_NUMBER() as varchar(20)) + ' line: '          
     + cast(ERROR_LINE() as varchar(20)) + ' '           
        + ERROR_MESSAGE() + ' > '           
        + ERROR_PROCEDURE()          

    RETURN @ErrorCode -- =0 if success,  <>0 if failure          

END CATCH  

Answer 1

You need also to consider results of XACT_STATE() function. Check the block Uncommittable Transactions and XACT_STATE on MSDN

---

LATER EDIT

---

I changed my mind about a possible solution :) Finally I got your situation reproduced. Look. I took your procedure, removed all code except the code related to exception handling and try\catch logic and added expression which will cause object name resolution error (fatal error). Here's what I got

CREATE PROCEDURE [dbo].[proc_PurchaseOrder_Create]         
AS          
SET NOCOUNT ON;          
BEGIN TRY          

    BEGIN TRAN   

    print 'before'
    select * from ##global_tmp_table; --> fatal error here
    print 'after'

    COMMIT TRAN         
END TRY           
BEGIN CATCH
    print 'catch'
    IF @@TRANCOUNT > 0 ROLLBACK          
END CATCH  

Ok, now if I try to execute this procedure I got the following output

before Msg 208, Level 16, State 0, Procedure proc_PurchaseOrder_Create, Line 29 Invalid object name '##global_tmp_table'. Msg 266, Level 16, State 2, Procedure proc_PurchaseOrder_Create, Line 29 Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

So, what happened? - Our transaction remains open - you can check this by executing print @@TRANCOUNT in the same window

why this happened? - because the catch block can't catch fatal errors - from the same MSDN article I mentioned earlier

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct: Compile errors, such as syntax errors, that prevent a batch from running. Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

Before we will fix this situation you need to call ROLLBACK in the window where you tried to execute stored procedure in order to finally close our transaction.

And how we can fix that? - we just need to set XACT_ABORT ON - you can find an example in all the same article in the Using TRY…CATCH with XACT_STATE block

So, finally, our test procedure should look like this

ALTER PROCEDURE [dbo].[proc_PurchaseOrder_Create]         
AS          
SET NOCOUNT ON;  
SET XACT_ABORT ON; --> the only change
BEGIN TRY          

    BEGIN TRAN   

    print 'before'
    select * from ##global_tmp_table;
    print 'after'

    COMMIT TRAN         
END TRY           
BEGIN CATCH
    print 'catch'
    IF @@TRANCOUNT > 0 ROLLBACK          
END CATCH 

And now, in a case of fatal error database engine will automatically roll back the transaction and you will get only the expected error message

before Msg 208, Level 16, State 0, Procedure proc_PurchaseOrder_Create, Line 31 Invalid object name '##global_tmp_table'.