title: SQL Server transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements
body: <p>In the below stored procedure, I'm getting the error mentioned in some cases, what am I doing wrong here?</p>
  
  <p><strong>Error:</strong></p>
  
  <blockquote>
    <p>Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1</p>
  </blockquote>
  
  <p>Stored procedure:</p>
  
  <pre><code>SET QUOTED_IDENTIFIER OFF 
  GO
  SET ANSI_NULLS ON 
  GO
  
  CREATE PROCEDURE [dbo].[prcInsDataFeesForVersion]   
      (@ContractId  INT,
       @UserId      INT,
       @VersionNo   INT,
       @SelectAll   BIT,
       @SubProductList NVARCHAR(MAX),
       @ConvRateFlag BIT)    
  AS  
  BEGIN  
      SET NOCOUNT ON  
  
      DECLARE @RetVal INT,  
              @ErrMsg VARCHAR(255),
              @FunctionId INT = 1234,    
              @AppId VARCHAR(20) = 'ID1021',
              @FeesOverrideFlg BIT = 1,
              @ContractProdFeeVerSummId INT
  
      CREATE TABLE #tblMasterData
      (
          ProdCd INT,
          ProdName NVARCHAR(255),
          SubProdCd INT,
          SubProdName NVARCHAR(255),
          ProdTypeCd INT,
          ProdDeliveryTypCd INT, 
          ProdStatusCd INT,
          ProdStatusDt DATETIME,
          CreatedDate DATETIME
      )
  
      CREATE TABLE #tblContractInterCulturalDataFiltered
      (
                          ContractID              INT
                        , ContractProdID          INT
                        , ProdCd                  INT
                        , ProdName                NVARCHAR(255)
                        , ContractSubProdID       INT
                        , SubProdCd               INT
                        , SubProdName             NVARCHAR(255)
                        , ContractProdStatusCd    INT
                        , ContractProdStatusDt    DATETIME
                        , ContractSubProdStatusCd INT
                        , ContractSubProdStatusDt DATETIME
                        , ContractProdFeeId       INT
                        , FeeTypCd                INT
                        , RowMatrixParameterCd    INT
                        , CreatedDate             DATETIME
                        )
  
                CREATE TABLE #tblContractInterCulturalDataFilteredInsert
                        (
                          ContractID               INT
                        , ContractProdID           INT
                        , ProdCd                   INT
                        , ProdName                 NVARCHAR(255)
                        , ContractSubProdID        INT
                        , SubProdCd                INT
                        , SubProdName              NVARCHAR(255)
                        , ContractProdStatusCd     INT
                        , ContractProdStatusDt     DATETIME
                        , ContractSubProdStatusCd  INT
                        , ContractSubProdStatusDt  DATETIME
                        , FeeTypCd                 INT
                        , FeeDeterminantCd         INT
                        , RowMatrixParameterCd     INT
                        , ProdFeeCurrCd            INT
                        , RowParameterValueCd      INT
                        , ColumnParameterValueCd   INT
                        , ProdFeePct               REAL
                        , RangeFromDayCnt          INT
                        , RangeToDayCnt            INT
                        , ProdFeeLevelMinAmt       DECIMAL
                        , ProdFeeLevelMaxAmt       DECIMAL
                        , ProdFeeAmt               DECIMAL
                        , ContractProdFeeId        INT
                        , CreatedDate              DATETIME
                        )
  
                CREATE TABLE #tblContractInterCulturalDataFilteredUpdate
                        (
                          ContractID               INT
                        , ContractProdID           INT
                        , ProdCd                   INT
                        , ProdName                 NVARCHAR(255)
                        , ContractSubProdID        INT
                        , SubProdCd                INT
                        , SubProdName              NVARCHAR(255)
                        , ContractProdStatusCd     INT
                        , ContractProdStatusDt     DATETIME
                        , ContractSubProdStatusCd  INT
                        , ContractSubProdStatusDt  DATETIME
                        , FeeTypCd                 INT
                        , FeeDeterminantCd         INT
                        , RowMatrixParameterCd     INT
                        , ProdFeeCurrCd            INT
                        , RowParameterValueCd      INT
                        , ColumnParameterValueCd   INT
                        , ProdFeePct               REAL
                        , RangeFromDayCnt          INT
                        , RangeToDayCnt            INT
                        , ProdFeeLevelMinAmt       DECIMAL
                        , ProdFeeLevelMaxAmt       DECIMAL
                        , ProdFeeAmt               DECIMAL
                        , ContractProdFeeId        INT
                        , CreatedDate              DATETIME
                        )             
  
                CREATE TABLE #tblSelContractSubProds
                (             
                        ContractSubProdId            INT
                )
  
                BEGIN TRY  
  
                BEGIN TRANSACTION
  
                IF(@SelectAll=0 AND @SubProductList IS NOT NULL)
                BEGIN
                        INSERT INTO #tblSelContractSubProds 
                        SELECT intValue FROM dbo.udfSplit(@SubProductList,',',1)
                END
  
                INSERT INTO #tblMasterData(
                          ProdCd
                        , ProdName
                        , SubProdCd
                        , SubProdName
                        , ProdTypeCd
                        , ProdDeliveryTypCd  
                        , ProdStatusCd
                        , ProdStatusDt
                        , CreatedDate
                        )
                        SELECT P.ProdCd
                        , PC.Descr
                        , S.SubProdCd
                        , SC.Descr
                        , P.ProdTypeCd
                        , P.ProdDeliveryTypCd
                        , P.ProdStatusCd
                        , P.ProdStatusDt
                        , GETDATE() 
                        FROM tblProduct(NOLOCK) as P
                        INNER JOIN tblCode(NOLOCK) as PC  ON P.ProdCd=PC.Cd and PC.Typ=1053
                        INNER JOIN tblSubProdDetail(NOLOCK) as S ON P.ProdCd=S.ProdCd
                        INNER JOIN tblCode(NOLOCK) as SC ON S.SubProdCd=SC.Cd and SC.Typ=1053
                        WHERE P.ProdDeliveryTypCd=3 and P.ProdStatusCd=1 
  
                IF(@SelectAll=1)
                BEGIN
                     INSERT INTO #tblContractInterCulturalDataFiltered(
                                        ContractID                        
                                      , ContractProdID                    
                                      , ProdCd                                   
                                      , ProdName    
                                      , ContractSubProdID                                
                                      , SubProdCd                         
                                      , SubProdName                       
                                      , ContractProdStatusCd              
                                      , ContractProdStatusDt              
                                      , ContractSubProdStatusCd    
                                      , ContractSubProdStatusDt    
                                      , ContractProdFeeId
                                      , FeeTypCd    
                                      , RowMatrixParameterCd
                                      , CreatedDate                       
                               )
                               SELECT DISTINCT CP.ContractId,
                               CP.ContractProdId,
                               CP.ProdCd,
                               CPC.Descr,
                               CSP.ContractSubProdID,
                               CSP.SubProdCd,
                               CSPC.Descr,
                               CP.ContractProdStatusCd,
                               CP.ContractProdStatusDt,
                               CSP.ContractSubProdStatusCd,
                               CSP.ContractSubProdStatusDt,
                               CPF.ContractProdFeeId,
                               CPF.FeeTypCd,
                               CPF.RowMatrixParameterCd,
                               Getdate()
                               FROM #tblMasterData(NOLOCK) MD
                               INNER JOIN tblContractProd(NOLOCK) CP ON MD.ProdCd=CP.ProdCd
                               INNER JOIN tblContractSubProd(NOLOCK) CSP ON CP.ContractProdId=CSP.ContractProdId
                               INNER JOIN tblCode(NOLOCK) CPC ON CP.ProdCd=CPC.Cd and CPC.Typ=1053
                               INNER JOIN tblCode(NOLOCK) CSPC ON CSP.SubProdCd=CSPC.Cd and CSPC.Typ=1053
                               INNER JOIN  tblContractProdFee(NOLOCK) CPF ON CP.ContractProdID=CPF.ContractProdId AND CSP.ContractSubProdID=CPF.ContractSubProdId 
                               WHERE ContractId=@ContractId AND CP.ContractProdStatusCd=1 AND CSP.ContractSubProdStatusCd=1 
                               AND CPF.ContractProdFeeTypStatusCd=1 AND CP.StandardProdIndCd=1 AND CSP.StdSubProdIndCd=1
                END
                ELSE
                BEGIN
                        IF(@SubProductList IS NOT NULL)
                        BEGIN
                        INSERT INTO #tblContractInterCulturalDataFiltered(
                                        ContractID                        
                                      , ContractProdID                    
                                      , ProdCd                                   
                                      , ProdName    
                                      , ContractSubProdID                                
                                      , SubProdCd                         
                                      , SubProdName                       
                                      , ContractProdStatusCd              
                                      , ContractProdStatusDt              
                                      , ContractSubProdStatusCd    
                                      , ContractSubProdStatusDt
                                      , ContractProdFeeId   
                                      , FeeTypCd    
                                      , RowMatrixParameterCd
                                      , CreatedDate                       
                               )
                               SELECT DISTINCT CP.ContractId,
                               CP.ContractProdId,
                               CP.ProdCd,
                               CPC.Descr,
                               CSP.ContractSubProdID,
                               CSP.SubProdCd,
                               CSPC.Descr,
                               CP.ContractProdStatusCd,
                               CP.ContractProdStatusDt,
                               CSP.ContractSubProdStatusCd,
                               CSP.ContractSubProdStatusDt,
                               CPF.ContractProdFeeId,
                               CPF.FeeTypCd,
                               CPF.RowMatrixParameterCd,
                               Getdate()
                               FROM #tblMasterData(NOLOCK) MD
                               INNER JOIN tblContractProd(NOLOCK) CP ON MD.ProdCd=CP.ProdCd
                               INNER JOIN tblContractSubProd(NOLOCK) CSP ON CP.ContractProdId=CSP.ContractProdId
                               INNER JOIN tblCode(NOLOCK) CPC ON CP.ProdCd=CPC.Cd and CPC.Typ=1053
                               INNER JOIN tblCode(NOLOCK) CSPC ON CSP.SubProdCd=CSPC.Cd and CSPC.Typ=1053
                               INNER JOIN tblContractProdFee(NOLOCK) CPF ON CP.ContractProdID=CPF.ContractProdId AND CSP.ContractSubProdID=CPF.ContractSubProdId
                               INNER JOIN #tblSelContractSubProds(NOLOCK) SCSP ON CSP.ContractSubProdId=SCSP.ContractSubProdId
                               WHERE ContractId=@ContractId AND CP.ContractProdStatusCd=1 AND CSP.ContractSubProdStatusCd=1 
                               AND CPF.ContractProdFeeTypStatusCd=1 AND CP.StandardProdIndCd=1 AND CSP.StdSubProdIndCd=1
                        END
                END
  
                INSERT INTO #tblContractInterCulturalDataFilteredUpdate(
                          ContractID                        
                        , ContractProdID                    
                        , ProdCd                                   
                       , ProdName            
                        , ContractSubProdID                        
                        , SubProdCd                         
                        , SubProdName                       
                        , ContractProdStatusCd              
                        , ContractProdStatusDt              
                        , ContractSubProdStatusCd    
                        , ContractSubProdStatusDt    
                        , FeeTypCd
                        , FeeDeterminantCd
                        , RowMatrixParameterCd
                        , ProdFeeCurrCd
                        , RowParameterValueCd
                        , ColumnParameterValueCd
                        , ProdFeePct
                        , RangeFromDayCnt
                        , RangeToDayCnt
                        , ProdFeeLevelMinAmt
                        , ProdFeeLevelMaxAmt
                        , ProdFeeAmt
                        , ContractProdFeeId
                        , CreatedDate                       
                )
                SELECT DISTINCT CICF.ContractId,
                CICF.ContractProdId,
                CICF.ProdCd,
                CICF.ProdName,
                CICF.ContractSubProdID,
                CICF.SubProdCd,
                CICF.SubProdName,
                CICF.ContractProdStatusCd,
                CICF.ContractProdStatusDt,
                CICF.ContractSubProdStatusCd,
                CICF.ContractSubProdStatusDt,
                CPFM.FeeTypCd,
                CPFM.FeeDeterminantCd,
                CPFM.RowMatrixParameterCd,
                CPFM.ProdFeeCurrCd,
                CPFM.RowParameterValueCd,
                CPFM.ColumnParameterValueCd,
                CPFM.ProdFeePct,
                CPFM.RangeFromDayCnt,
                CPFM.RangeToDayCnt,
                CPFM.ProdFeeLevelMinAmt,
                CPFM.ProdFeeLevelMaxAmt,
                CPFM.ProdFeeAmt,
                CICF.ContractProdFeeId,
                Getdate()
                FROM #tblContractInterCulturalDataFiltered(NOLOCK) CICF
                INNER JOIN tblContractProdFeeMatrix(NOLOCK) CPFM ON CICF.ProdCd=CPFM.FeeMatrixProdCd AND CICF.SubProdCd=CPFM.FeeMatrixSubProdCd AND CICF.FeeTypCd=CPFM.FeeTypCd
                INNER JOIN tblContractProdFeeMatrixVersion(NOLOCK) CPFMV ON CPFM.ContractProdFeeMatrixVersionId=CPFMV.ContractProdFeeMatrixVersionId 
                INNER JOIN tblContractProdFee(NOLOCK) CP ON CICF.ContractProdID=CP.ContractProdId AND CICF.ContractSubProdID=CP.ContractSubProdId AND CICF.ContractProdFeeId=CP.ContractProdFeeId
                AND CPFM.FeeDeterminantCd=CP.FeeDeterminantCd AND CPFM.RowMatrixParameterCd =CP.RowMatrixParameterCd
                WHERE CICF.ContractId=@ContractId AND CPFMV.ProdFeeMatrixVerNo=@VersionNo AND CICF.ContractProdStatusCd=1 AND CICF.ContractSubProdStatusCd=1 AND CP.ContractProdFeeTypStatusCd=1
                AND CPFM.RowMatrixParameterCd IN(6,4)
                AND EXISTS(SELECT PFM.ContractProdFeeId from tblProdFeeMatrix(NOLOCK)PFM WHERE CICF.ContractProdFeeId=PFM.ContractProdFeeId)
  
                IF EXISTS(Select 1 From #tblContractInterCulturalDataFilteredUpdate(NOLOCK))
                BEGIN
                        DELETE PFM
                        FROM tblProdFeeMatrix(NOLOCK) PFM
                        INNER JOIN #tblContractInterCulturalDataFilteredUpdate(NOLOCK) TIFU ON TIFU.ContractProdFeeId=PFM.ContractProdFeeId
                END
  
                INSERT INTO #tblContractInterCulturalDataFilteredInsert(
                          ContractID                        
                        , ContractProdID                    
                        , ProdCd                                   
                        , ProdName            
                        , ContractSubProdID                        
                        , SubProdCd                         
                        , SubProdName                       
                        , ContractProdStatusCd              
                        , ContractProdStatusDt              
                        , ContractSubProdStatusCd    
                        , ContractSubProdStatusDt    
                        , FeeTypCd
                        , FeeDeterminantCd
                        , RowMatrixParameterCd
                        , ProdFeeCurrCd
                        , RowParameterValueCd
                        , ColumnParameterValueCd
                        , ProdFeePct
                        , RangeFromDayCnt
                        , RangeToDayCnt
                        , ProdFeeLevelMinAmt
                        , ProdFeeLevelMaxAmt
                        , ProdFeeAmt
                        , ContractProdFeeId
                        , CreatedDate                       
                )
                SELECT DISTINCT CICF.ContractId,
                CICF.ContractProdId,
                CICF.ProdCd,
                CICF.ProdName,
                CICF.ContractSubProdID,
                CICF.SubProdCd,
                CICF.SubProdName,
                CICF.ContractProdStatusCd,
                CICF.ContractProdStatusDt,
                CICF.ContractSubProdStatusCd,
                CICF.ContractSubProdStatusDt,
                CPFM.FeeTypCd,
                CPFM.FeeDeterminantCd,
                CPFM.RowMatrixParameterCd,
                CPFM.ProdFeeCurrCd,
                CPFM.RowParameterValueCd,
                CPFM.ColumnParameterValueCd,
                CPFM.ProdFeePct,
                CPFM.RangeFromDayCnt,
                CPFM.RangeToDayCnt,
                CPFM.ProdFeeLevelMinAmt,
                CPFM.ProdFeeLevelMaxAmt,
                CPFM.ProdFeeAmt,
                CICF.ContractProdFeeId,
                Getdate()
                FROM #tblContractInterCulturalDataFiltered(NOLOCK) CICF
                INNER JOIN tblContractProdFeeMatrix(NOLOCK) CPFM ON CICF.ProdCd=CPFM.FeeMatrixProdCd AND CICF.SubProdCd=CPFM.FeeMatrixSubProdCd AND CICF.FeeTypCd=CPFM.FeeTypCd
                INNER JOIN tblContractProdFeeMatrixVersion(NOLOCK) CPFMV ON CPFM.ContractProdFeeMatrixVersionId=CPFMV.ContractProdFeeMatrixVersionId 
                INNER JOIN tblContractProdFee(NOLOCK) CP ON CICF.ContractProdID=CP.ContractProdId AND CICF.ContractSubProdID=CP.ContractSubProdId AND CICF.ContractProdFeeId=CP.ContractProdFeeId
                AND CPFM.FeeDeterminantCd=CP.FeeDeterminantCd AND CPFM.RowMatrixParameterCd =CP.RowMatrixParameterCd
                WHERE CICF.ContractId=@ContractId AND CPFMV.ProdFeeMatrixVerNo=@VersionNo AND CICF.ContractProdStatusCd=1 AND CICF.ContractSubProdStatusCd=1 AND CP.ContractProdFeeTypStatusCd=1 
                AND CPFM.RowMatrixParameterCd IN(6,4)
  
                IF EXISTS(Select 1 From #tblContractInterCulturalDataFilteredInsert(NOLOCK))
                BEGIN
                               INSERT INTO [dbo].[tblProdFeeMatrix] 
                                      ([ContractProdFeeId]
                                      ,[ContractBundleFeeId]
                                      ,[ContractProdBundleAncilFeeId]
                                      ,[RowParameterValueCd]
                                      ,[ColumnParameterValueCd]
                                      ,[ProdFeeAmt]
                                      ,[ProdFeeCurrCd]
                                      ,[ProdFeePct]
                                      ,[RangeFromDayCnt]
                                      ,[RangeToDayCnt]
                                      ,[RangeFromPct]
                                      ,[RangeToPct]
                                      ,[RangeFromAmt]
                                      ,[RangeToAmt]
                                      ,[ProdFeeLevelMinAmt]
                                      ,[ProdFeeLevelMaxAmt]
                                      ,[FeeNotApplyIndCd]
                                      ,[CreateId]
                                      ,[CreateDt]
                                      ,[UpdateId]
                                      ,[UpdateDt]
                                      ,[FunctionId])
  
                                      SELECT DISTINCT
                                      TIFI.ContractProdFeeID,
                                      0,
                                      0,
                                      TIFI.RowParameterValueCd,
                                      TIFI.ColumnParameterValueCd,
                                      TIFI.ProdFeeAmt,
                                      TIFI.ProdFeeCurrCd,
                                      TIFI.ProdFeePct,
                                      TIFI.RangeFromDayCnt,
                                      TIFI.RangeToDayCnt,
                                      0,
                                      0,
                                      0,
                                      0,
                                      TIFI.ProdFeeLevelMinAmt,
                                      TIFI.ProdFeeLevelMaxAmt,
                                      0,
                                      @UserId,
                                      GETDATE(),
                                      @UserId,
                                      GETDATE(),
                                      @FunctionId
                                      FROM #tblContractInterCulturalDataFilteredInsert(NOLOCK) TIFI
             END
  
                IF(@ConvRateFlag=1)
                BEGIN
                        EXEC [ASSET_DB].[dbo].[prcCalculateRates] @UserId,@ContractId,@SelectAll,@SubProductList,@FeesOverrideFlg
                END
  
                COMMIT TRANSACTION
  
                SELECT @RetVal=0
                GOTO CLEARTEMPTABLES
                GOTO DONE
  
             END TRY  
  
                BEGIN CATCH  
                               SELECT @ErrMsg = ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE() + ' (Error No. ' + Ltrim(Str(ERROR_NUMBER())) + ') ' + ' at line # ' + Ltrim(Str(ERROR_LINE()))  
                                             ,@RetVal = 200 
                               GOTO CLEARTEMPTABLES
                               GOTO ERROR  
                END CATCH    
  
  
          ERROR:  
                ROLLBACK TRANSACTION
                RAISERROR(@ErrMsg,16,1 ) 
  
         CLEARTEMPTABLES:
  
                BEGIN TRY
                            IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblMasterData]') )
                               BEGIN
                                 TRUNCATE TABLE  #tblMasterData
                                 DROP TABLE  #tblMasterData
                               END
                               IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblContractInterCulturalDataFiltered]') )
                               BEGIN
                                      TRUNCATE TABLE  #tblContractInterCulturalDataFiltered
                                      DROP TABLE  #tblContractInterCulturalDataFiltered
                               END
                               IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblContractInterCulturalDataFilteredInsert]') )
                               BEGIN
                                      TRUNCATE TABLE  #tblContractInterCulturalDataFilteredInsert
                                      DROP TABLE  #tblContractInterCulturalDataFilteredInsert
                               END
                               IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblContractInterCulturalDataFilteredUpdate]') )
                               BEGIN
                                      TRUNCATE TABLE  #tblContractInterCulturalDataFilteredUpdate
                                      DROP TABLE  #tblContractInterCulturalDataFilteredUpdate
                               END
                               IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblSelContractSubProds]') )
                               BEGIN
                                      TRUNCATE TABLE  #tblSelContractSubProds
                                      DROP TABLE  #tblSelContractSubProds
                               END
                END TRY
                BEGIN CATCH
                        SELECT @ErrMsg = ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE() + ' (Error No. ' + Ltrim(Str(ERROR_NUMBER())) + ') ' + ' at line # ' + Ltrim(Str(ERROR_LINE()))  
                                                    ,@RetVal = 201
                        GOTO ERROR  
                END CATCH
  
  
          DONE:  
  
                SET NOCOUNT OFF  
                Return (@RetVal)    
  END    
  </code></pre>