CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2016
mehtat_90
mehtat_90

Reputation: 628

Stored procedure error EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

I have a stored procedure which works fine when I remove Select 1/0 (this is to test if stored procedure catches error) but when I have Select 1/0 in the stored procedure, it throws an error

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1

I tried the apply solution from TSQL Try / Catch within Transaction or vice versa? but still I still get the same error.

ALTER PROCEDURE [dbo].[PTA_Processing_D365]
AS
    BEGIN TRY
    BEGIN TRANSACTION test1
        UPDATE table A
        ....
        ......
        ......;

        INSERT INTO table A(.....)
            SELECT .....
            FROM table b
            WHERE .....;

        SELECT 1/0

        ROLLBACK TRANSACTION test1
    END TRY
    BEGIN CATCH
        INSERT INTO tableA_ErrorLog ([ExecutedDate], [ERROR_NUMBER], [ERROR_SEVERITY], 
                                     [ERROR_STATE], [ERROR_PROCEDURE], [ERROR_LINE], [ERROR_MESSAGE])
            SELECT 
                GETDATE(), ERROR_NUMBER(), ERROR_SEVERITY(), 
                ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE()
   END CATCH

Upvotes: 0

Views: 128

Answers (1)

S3S
S3S

Reputation: 25112

Since your current set up doesn't allow you to COMMIT the transaction ever, you'll want to add a COMMIT to the TRY block, and move the ROLLBACK to the CATCH block. Something like the below. Again, read up on Erland's lengthy blog on error handling for better ways and other things you should include like SET XACT_ABORT ON:

ALTER Procedure [dbo].[PTA_Processing_D365]
as

BEGIN TRY
BEGIN TRANSACTION test1

    update table A
    ....
    ......
    ......
    ;
    insert into table A
    (.....
    ....
    ....)
    Select .....
    ....
    ..... from table b
    where .....;

    select 1/0

COMMIT TRAN test1
END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN test1
    INSERT INTO tableA_ErrorLog
               ([ExecutedDate]
               ,[ERROR_NUMBER]
               ,[ERROR_SEVERITY]
               ,[ERROR_STATE]
               ,[ERROR_PROCEDURE]
               ,[ERROR_LINE]
               ,[ERROR_MESSAGE])
    select GetDate (), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE()
END CATCH

Upvotes: 2

Related Questions