SQL Server - Nested transactions in a stored procedure

nested, sql-server-2008, transactions

Solution

There are no autonomous transactions in SQL Server. You may see `@@TRANCOUNT` increase beyond 1, but a rollback affects the whole thing.

EDIT asked to point to documentation. Don't know of the topic that documents this explicitly, but I can show it to you in action.

USE tempdb;
GO

Inner proc:

CREATE PROCEDURE dbo.sp2
    @trip BIT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION;

    PRINT @@TRANCOUNT;

    IF @trip = 1
    BEGIN
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
    END
    ELSE
    BEGIN   
        IF @@TRANCOUNT > 0
            COMMIT TRANSACTION;
    END

    PRINT @@TRANCOUNT;
END
GO

Outer proc:

CREATE PROCEDURE dbo.sp1
    @trip BIT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION;

    PRINT @@TRANCOUNT;

    BEGIN TRY
        EXEC dbo.sp2 @trip = @trip;
    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE();
    END CATCH

    PRINT @@TRANCOUNT;

    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION;

    PRINT @@TRANCOUNT;
END
GO

So now let's call it and let everything commit:

EXEC dbo.sp1 @trip = 0;

Results:

1 2 1 1 0

Now let's call it and roll back the inner procedure:

EXEC dbo.sp1 @trip = 1;

Results:

1 2 0 <-- notice that a rollback here rolled back both Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0. 0 0

Problem

Lets say this is the situation: ``` [Stored Proc 1] BEGIN BEGIN TRANSACTION ... exec sp 2 COMMIT END ``` Now, if SP 2 - rolls back for whatever reason, does SP 1 - commit or rollback or throw exception? Thanks.

Original source