Transaction error in stored procedure that is not using BEGIN or END TRANSACTION

Question

I have a stored procedure "let's call it MY_NEW_SP" in which I'm not using BEGIN TRY / BEGIN CATCH. but, when I'm excecuting this SP (MY_NEW_SP), I get the following error:

Msg 266, Level 16, State 2, Procedure <MY_NEW_SP>, Line 132
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

This new stored procedure makes a big select basically, no transactions are made "in the sense of make DML operations on tables (INSERT, DELETE, UPDATE)", but in temp tables "i.e. #tmp".

I'm thinking this transaction error is due I'm using SET XACT_ABORT ON; in other stored procedures, but, I'm not sure.

I follow what it is said here: C. Using TRY...CATCH with XACT_STATE

The basic structure of the stored procedure that uses SET XACT_ABORT ON; is as follows:

IF NOT EXISTS (SELECT * FROM sysobjects WHERE TYPE = 'P' AND NAME = 'PROCEP_NEW_SP' )
BEGIN
    EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE PROCEP_NEW_SP AS'
END
GO

ALTER PROCEDURE PROCEP_NEW_SP 
(
    @ID_TABLE INT
)
AS
BEGIN       
    DECLARE @TBL_CONSECUTIVE TABLE ( LOG_CONSECUTIVE INT );

    SET XACT_ABORT ON;
    BEGIN TRANSACTION
    BEGIN TRY

        IF ISNULL(@ID_TABLE, -1) = -1 
        BEGIN
            SET @ID_TABLE = 1; 
            DELETE FROM @TBL_CONSECUTIVE;

            INSERT INTO T_BH_LOG_TABLE (ASO_NCODE, CHA_NCODE, TSO_NCODE, 
            MSO_DACTION_DATE, MSO_CRESULT, MSO_CCAUSE_FAILURE)
            OUTPUT INSERTED.MSO_NCODE INTO @TBL_CONSECUTIVE 
            SELECT @ASO_NCODE, ISNULL(@CHA_NCODE, 1), ISNULL(@TSO_NCODE, 1), 
            GETDATE() AS MSO_DACTION_DATE, @CST_FAIL_OR_SUC, @CST_GENERIC_MSG;

            IF (XACT_STATE()) = 1 
            BEGIN 
                COMMIT TRANSACTION;
            END 

            SELECT NULL Id_table, 'Failed' Result_process, 'Parameter (ID_TABLE) is required.' Result_process_message;

            RETURN;
        END

        -- Operation: 
        UPDATE MY_TABLE
        SET NAME = 'SAMPLE' 
        WHERE ID_TABLE = @ID_TABLE;                 

        IF (XACT_STATE()) = 1 
        BEGIN 
            COMMIT TRANSACTION;
        END

    END TRY
    BEGIN CATCH 
        ROLLBACK TRANSACTION; 

        INSERT INTO T_BH_LOG_TABLE (ASO_NCODE, CHA_NCODE, TSO_NCODE, 
        MSO_DACTION_DATE, MSO_CRESULT, MSO_CCAUSE_FAILURE) 
        OUTPUT INSERTED.MSO_NCODE INTO @TBL_CONSECUTIVE 
        SELECT 1 AS ASO_NCODE, 1, 1 As TSO_NCODE, 
        GETDATE() AS MSO_DACTION_DATE, @CST_FAIL_OR_SUC, @CST_GENERIC_MSG; 

        SELECT NULL Id_table, 'Failed' Result_process, 'Internal error. See log # (' + CAST(L.LOG_CONSECUTIVE AS NVARCHAR) + ') for more details.' Result_process_message;
        FROM @TBL_CONSECUTIVE L; 

        RETURN;
    END CATCH
END;

I really don't know if by using SET XACT_ABORT ON; is causing this kind of error.

Anyone can point me in the right direction for solve this issue?

Does this help? https://stackoverflow.com/questions/21930156/transaction-count-after-execute-indicates-a-mismatching-number-of-begin-and-comm — Dave Costa, May 13 '20 at 14:35
@DaveCosta it did, thank you. I added these lines before called my new stored procedure: `IF XACT_STATE() = 1 AND @@TRANCOUNT > 0 ROLLBACK TRANSACTION;`. The error continues, though. I'm wondering if the way I made the structure is correct and / or if there's a way to check where this error is rising, I'll check SQL profiler for any clues. I appreciate your help — Marco Aurelio Fernandez Reyes, May 13 '20 at 14:52
Do you have implicit transactions turned on by any chance? (https://stackoverflow.com/questions/4986683/difference-between-implicit-and-explicit-transaction) — Alex, Jun 04 '20 at 06:09

Transaction error in stored procedure that is not using BEGIN or END TRANSACTION

0 Answers0