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

Viewed 15318

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  
1 Answers
Related