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

Question

I am trying to write logs for stored procedures. I am putting my sp in try block and trying to catch exceptions in catch block . If there are any exceptions I am ROLLING BACK the transaction or I am COMMITING the transaction.

USE [RPM_BROKER_TOOL]
GO
/****** Object:  StoredProcedure [dbo].[RPM_PROC_UPDATE_BROKER_SETTINGS]    Script Date: 09/24/2015 16:28:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- AUTHOR     : ROHIT NAIK
-- CREATE DATE: 31-JULY-2015
-- DESCRIPTION: Update Broker Tool settings.
-- =============================================
ALTER PROCEDURE [dbo].[RPM_PROC_UPDATE_BROKER_SETTINGS](
                        @pk_broker_param int,
                        @companyID int,
                        --@BrokerSetting xml
                        @broker_param_id int,
                        @fixed_param_id int,
                        @Exchange_Type varchar(10),
                        @Parameter_Description varchar(100),
                        @DataSize int,
                        @DataType varchar(2),
                        @DataValue varchar(100),
                        @Dialog_Filter varchar(100),
                        @ListOfValues varchar(500),
                        @isMandatory BIT,
                        @CREATED_IN_MODULE varchar(30),
                        @CREATE_LOGIN_NAME varchar(10),
                        @CREATED_ON DATETIME,
                        @Field_Type varchar(5),
                        @Updated_Login varchar(10),
                        @SequenceId int,
                        @Description varchar(1000)          
                        )

AS

DECLARE @Current_Timestamp DATETIME = current_timestamp,
        @RowsInserted int,
        @ErrorMessage varchar(1000),
        @ErrorTable RPM_EXCEPTION_TYPE_TEMP ,
        @ProcName varchar(100)
        --@IN_Pk_Broker_Param int used for auditing

DECLARE @ID_MODULE INT,@DATA_VALUE VARCHAR(500)
SET @ID_MODULE = (SELECT ID_MODULE FROM FW_MODULES WHERE CD_MODULE = 'BROKER APP')
SET @ProcName = (SELECT OBJECT_NAME(@@PROCID))
BEGIN

BEGIN TRY
    BEGIN TRANSACTION

SELECT @DATA_VALUE=DATAVALUE FROM RPM_BROKER_TOOL_CALIBRATION_PARAMETERS WHERE AUDIT_ACTIVE=1 AND FIXED_PARAMETER_ID=@fixed_param_id
IF ISNULL(@DATA_VALUE,'')<>@DataValue
BEGIN
        select 1/0
        INSERT INTO RPM_BROKER_TOOL_CALIBRATION_PARAMETERS(
                        ID_COMPANY,
                        --ID_BROKER_PARAM,
                        FIXED_PARAMETER_ID,
                        EXCHANGE_TYPE,
                        PARAMETER_DESCRIPTION,
                        DATASIZE,
                        DATATYPE,
                        DATAVALUE,
                        DAILOG_FILE_FILTER,
                        LOV,
                        isMandatory,
                        CREATED_IN_MODULE,
                        CREATE_LOGIN_NAME,
                        CREATE_TIME_STAMP,
                        UPDATED_IN_MODULE,
                        UPDATE_LOGIN_NAME,
                        UPDATE_TIME_STAMP,
                        AUDIT_ACTIVE,
                        AUDIT_TYPE,
                        AUDIT_FROM_DATE,
                        AUDIT_TO_DATE,                      
                        SESSION_ID,
                        Field_Type,
                        ID_SEQUENCE,
                        Description
                        )

                VALUES (                        
                        @companyID,
                        --@broker_param_id,
                        @fixed_param_id,
                        @Exchange_Type,
                        @Parameter_Description,
                        @DataSize,
                        @DataType,
                        @DataValue,
                        @Dialog_Filter,
                        @ListOfValues,
                        @isMandatory,
                        @CREATED_IN_MODULE,
                        @CREATE_LOGIN_NAME,
                        @CREATED_ON,
                        @CREATED_IN_MODULE,
                        @Updated_Login,
                        @Current_Timestamp,
                        1,
                        'E',
                        @Current_Timestamp,
                        '12/31/2099',
                        null,
                        @Field_Type,
                        @SequenceId,
                        @Description 
                        )
                    SET @RowsInserted = @@ROWCOUNT

                    SET @ErrorMessage = 'Number of rows updated: ' + convert(varchar(100),@RowsInserted) 
                INSERT INTO @ErrorTable (COMPANY_ID,ID_OBJECT,LOGIN_NAME,MODULE_CODE,PROC_NAME,TIME_STAMP,BUSSINESS_MODULE,EXCEPTION_CATEGORY,EXCEPTION_LEVEL,EXCEPTION_DESCRIPTION)
                VALUES (@companyID,NULL,@CREATE_LOGIN_NAME,@CREATED_IN_MODULE,@ProcName,@Current_Timestamp,NULL,'Information','Normal',@ErrorMessage)

                UPDATE  RPM_BROKER_TOOL_CALIBRATION_PARAMETERS
                SET     AUDIT_ACTIVE = 0, AUDIT_TO_DATE = @Current_Timestamp,
                        UPDATE_TIME_STAMP = @Current_Timestamp, 
                        UPDATE_LOGIN_NAME = @Updated_Login,
                        UPDATED_IN_MODULE = @CREATED_IN_MODULE
                WHERE   PK_BROKER_PARAM = @pk_broker_param


        declare @Update_fields varchar(200)
        set @Update_fields = @Parameter_Description + ' modified'

        EXEC RPM_PROC_INSERT_AUDIT 
        '4016'
        ,@companyID
        ,@pk_broker_param
        --,@fixed_param_id
        ,1 
        ,'E'
        ,@Parameter_Description
        ,@Update_fields
        ,@CREATED_IN_MODULE
        ,@Updated_Login
        ,'BROKER APP SETTINGS'
        ,@CURRENT_TIMESTAMP



END
    COMMIT TRANSACTION
END TRY

BEGIN CATCH

    SET @ErrorMessage = 'Error : ' + ERROR_MESSAGE() + 'on #Line ' + convert(varchar(1000),ERROR_LINE())

    INSERT INTO @ErrorTable (COMPANY_ID,ID_OBJECT,LOGIN_NAME,MODULE_CODE,PROC_NAME,TIME_STAMP,BUSSINESS_MODULE,EXCEPTION_CATEGORY,EXCEPTION_LEVEL,EXCEPTION_DESCRIPTION)
                VALUES (@companyID,NULL,@CREATE_LOGIN_NAME,@CREATED_IN_MODULE,@ProcName,@Current_Timestamp,NULL,'Error','High',@ErrorMessage)
    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION
END CATCH

    EXEC update_db_log @ErrorTable

END

Answer 1

I don't know why you are getting the issue. But when I have done exactly like your code, I got same Transaction Count. Here is my code:

CREATE TABLE

CREATE TABLE [dbo].[Test](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
    [IsActive] [bit] NULL CONSTRAINT [DF_Emails_IsActive]  DEFAULT ((1))
)

SQL

BEGIN TRY
    BEGIN TRANSACTION
        Print(@@TRANCOUNT)
        Print('start')
        Print(@@TRANCOUNT)
        INSERT INTO Test VALUES ('First')
        select 1/0
        Print(@@TRANCOUNT)
        Print('never reach')
        INSERT INTO Test VALUES ('Second')
        Print(@@TRANCOUNT)
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    Print(@@TRANCOUNT)
    Print('catch called')
    INSERT INTO Test VALUES ('Third')
    Print(@@TRANCOUNT)
    Print('inserted')

    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION
END CATCH
SELECT * FROM Test 

And the statement before Rollback is also rolled back. (In your case, INSERT INTO @ErrorTable ... as per my comment. )

Here is the output:

1
start
1

(1 row(s) affected)

(0 row(s) affected)
1
catch called

(1 row(s) affected)
1
inserted

(0 row(s) affected)