An error occured while executing query with nested transactions

93 Views Asked by At

I encountered an error while trying to execute the query below.

if exists (select null from sys.sysobjects where type='P' and name = 'myProc')
    drop PROCEDURE myProc
go

create procedure myProc
as
begin
    set nocount on
    set xact_abort on

    begin try
        declare @trancount int = @@trancount

        if @trancount = 0
            begin tran
        else
            save tran MySave

        raiserror ('123213123',16,1)

        if @trancount = 0
            commit
    end try 
    begin catch
        if @trancount = 0
            rollback
        else
            if XACT_STATE() = 1
                rollback tran MySave
            else
                rollback    
    end catch
end
go

begin tran
    EXEC myProc

if @@TRANCOUNT >0
    rollback

the error is

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

I've read many topics about similar problems but can't get it clear so far what's the reason in my case. Could anyone explain me why I get it and what should I do to avoid it. Thanks in advance

upd. I can simplify the code of MyProc like

create procedure myProc
as
begin
    set nocount on
    set xact_abort on

    begin try
        begin tran
           raiserror ('123213123',16,1)
        commit
    end try 
    begin catch
        rollback
    end catch
end
go

It doesn't solve my problems. the same error occurs

1

There are 1 best solutions below

3
Giorgi Nakeuri On

Try this:

ALTER PROCEDURE myProc
AS
    BEGIN
        SET NOCOUNT ON
        SET XACT_ABORT ON

        BEGIN TRY
            DECLARE @trancount INT = @@trancount

            IF @trancount = 0
                BEGIN TRAN
            ELSE
                SAVE TRAN MySave

            RAISERROR ('123213123',16,1)

            IF @trancount = 0
                COMMIT
        END TRY 
        BEGIN CATCH
            IF XACT_STATE() <> 0
                AND @trancount = 0
                ROLLBACK TRANSACTION;
        END CATCH
    END
GO

BEGIN TRAN
EXEC myProc


IF @@TRANCOUNT > 0
    ROLLBACK