CODEWITHSUNDEEP
sql-servert-sql
ZedZip
ZedZip

Reputation: 6462

How to rollback transaction in a stored procedure?

When the internal SP tries to rollback transaction it completed with an error:

Msg 266, Level 16, State 2, Procedure ptest, Line 0 [Batch Start Line 37] Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

Is it possible to rollback transaction inside the internal SP?

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ptest]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[ptest] AS'
END
GRANT EXECUTE on [dbo].[ptest] to public;
GO
ALTER  PROCEDURE [dbo].[ptest]
@parrollback bit = 0
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT OFF
select @@TRANCOUNT as '@@TRANCOUNT:[ptest] '
if @parrollback is not null and @parrollback>0
    if @@TRANCOUNT>0 rollback tran;
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pcaller]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[pcaller] AS'
END
GRANT EXECUTE on [dbo].[pcaller] to public;
GO
ALTER  PROCEDURE [dbo].[pcaller]
AS
BEGIN
SET NOCOUNT ON
begin tran
select @@TRANCOUNT as '@@TRANCOUNT: before [ptest]'
exec ptest 1
select @@TRANCOUNT as '@@TRANCOUNT: after [ptest] '
if @@TRANCOUNT>0 rollback tran;
END
GO
-------------

exec pcaller 

/*
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ptest]') AND type in (N'P', N'PC'))
    drop proc pcaller
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ptest]') AND type in (N'P', N'PC'))
    drop proc ptest
*/

Upvotes: 0

Views: 2459

Answers (1)

lptr
lptr

Reputation: 6788

try not to handle parent transactions inside a child procedure (exception when XACT_STATE() = -1). Handle the transaction at the "execution" level that started it.

if a procedure is executed in a parent transaction, then create a savepoint and rollback to it when needed. capture the execution result of the child procedure and handle the transaction at the parent level (if the parent is the one that begun the transaction).

CREATE OR ALTER PROCEDURE [dbo].[ptest] @parrollback bit = 0
AS
BEGIN
    SET NOCOUNT ON
    SET XACT_ABORT OFF

    DECLARE @trancount INT = @@TRANCOUNT;

    IF @trancount = 0
    BEGIN
        BEGIN TRANSACTION;
    END
    ELSE
    BEGIN
        SAVE TRANSACTION MySavepoint;
    END

    --do stuff.........


    --when it is time to commit or check for errors
    --assume @parrollback is the main control criterium

    IF @parrollback = 1
    BEGIN
        IF @trancount = 0
        BEGIN
            ROLLBACK TRANSACTION;
            RETURN(0);
        END
        ELSE
        BEGIN
            ROLLBACK TRANSACTION MySavePoint
            RETURN (1); 
        END
    END

    --just handle @parrollback <> 1, for completeness of the test
    IF @trancount = 0
    BEGIN
        COMMIT TRANSACTION;
    END

    RETURN (0);
END
GO

CREATE OR ALTER  PROCEDURE dbo.pcaller
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @ptestexec INT;
    BEGIN TRANSACTION

    select @@TRANCOUNT as '@@TRANCOUNT: before [ptest]'
    EXEC @ptestexec = dbo.ptest @parrollback = 1;

    IF @ptestexec = 1
    BEGIN
        ROLLBACK TRANSACTION
    END
    ELSE
    BEGIN
        COMMIT TRANSACTION
    END


    --execute ptest, outside of a transaction
    EXEC @ptestexec = dbo.ptest @parrollback = 0;
    SELECT @@TRANCOUNT AS trancount1;

    EXEC @ptestexec = dbo.ptest @parrollback = 1;
    SELECT @@TRANCOUNT AS trancount2;

    --execute ptest, outside of a transaction
    BEGIN TRANSACTION;

    --ptest executed in a parent transaction
    EXEC @ptestexec = dbo.ptest @parrollback = 0;
    SELECT @@TRANCOUNT AS trancount3; --ptest does not affect the parent transactions

    COMMIT TRANSACTION --or rollback
END
GO

EXEC dbo.pcaller
GO

Upvotes: 1

Related Questions