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

Question

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

you have no commit tran... and that rollback would usually be in the catch. Any reason it's in the try? I can't reproduce this thought. — S3S, Jan 25 '19 at 15:36
I have rollback in try because if try fails then I need to erase all the data modifications made from the start of the transaction — mehtat_90, Jan 25 '19 at 15:39
Yeah, if there was an error then the catch block is executed (and you'd have the rollback there). How would you commit the tran if there wasn't an error with your current method of having the rollback in the try? [Check out Erland](http://www.sommarskog.se/error_handling/Part1.html) blog on error handling. [Small demo here](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=eb3cca2b187ae6a253ea26f390a51a80) — S3S, Jan 25 '19 at 15:41

score 2 · Accepted Answer · answered Jan 25 '19 at 15:49

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

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

1 Answers1