SQL Server - Nested transactions in a stored procedure

Question

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.

Answer 1

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