An error occured while executing query with nested transactions

Question

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

Giorgi Nakeuri · Answer 1 · 2015-01-30T10:00:10.307

0

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

edited Jan 30 '15 at 10:00

answered Jan 30 '15 at 08:00

Giorgi Nakeuri

35,155
8
47
75

trancount is a count of transaction before MyProc is processing. if trancount equals 0, I start a trancaction (begin tran) and then, if error occurs, rollback it, if @trancount >0 it means I already inside an outer transaction. so I make a save point – zagrr Jan 30 '15 at 08:57
@ Giorgi Nakeuri, yes it works. but I'm feeling it's not what I really wanted to get and before we resume, could you (or anybode else) think me up an example when i get into catch-block with XACT_STATE() = 1 – zagrr Jan 30 '15 at 11:48
@zagrr, comment line --SET XACT_ABORT ON – Giorgi Nakeuri Jan 30 '15 at 11:57

An error occured while executing query with nested transactions

1 Answers1