What is the right way to use multiple savepoints

Question

I'm using transactions for the first time, so I might be asking a silly question.

I want to insert data in 3 tables:

Table1(p1,p2,p3)
Table2(q1,q2)
Table3(t3,fk1,fk2)

For example, if something goes wrong and data cannot be inserted into Table2, data from Table1 will not be lost and Table3 remains unchanged (and viceversa).

I've tried two versions so far, but none of them is satisfying.

Version 1:

CREATE PROCEDURE InsertInto(@p1,@p2,@p3,@q1,@q2,@t3))
AS BEGIN

BEGIN TRAN
SET XACT_ABORT OFF

SAVE TRANSACTION point1
BEGIN TRY
    DECLARE @fk1 INT
    INSERT INTO Table1 VALUES (@p1,@p2,@p3)
    SELECT @fk1 = Table1.Id FROM Table1 WHERE Table1.p1 = @p1

    SAVE TRANSACTION point2
    BEGIN TRY
        DECLARE @fk2 INT
        INSERT INTO Table2 VALUES (@q1,@q2)
        SELECT @fk2 = Table2.Id FROM Table2 WHERE Table2.q1 = @q1

        SAVE TRANSACTION point3
        BEGIN TRY
            INSERT INTO Table3 VALUES (@t3, @fk1, @fk2)
            COMMIT TRAN
            END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION point3
            COMMIT TRAN
        END CATCH
        
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION point2
        COMMIT TRAN
    END CATCH

END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION point1
    COMMIT TRAN
END CATCH
END

But if data cannot be inserted in Table1, then possible data for Table2 is lost and I don't want to lose anything. So, I've tried to split it.

Version 2:

CREATE PROCEDURE InsertInto(@p1,@p2,@p3,@q1,@q2,@t3)
AS
BEGIN

BEGIN TRAN
SET XACT_ABORT OFF

SAVE TRANSACTION point1
BEGIN TRY
    DECLARE @fk1 INT
    INSERT INTO Table1 VALUES (@p1,@p2,@p3)
    SELECT @fk1 = Table1.Id FROM Table1 WHERE Table1.p1 = @p1
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION point1
    COMMIT TRAN
END CATCH

SAVE TRANSACTION point2
BEGIN TRY
    DECLARE @fk2 INT
    INSERT INTO Table2 VALUES (@q1,@q2)
    SELECT @fk2 = Table2.Id FROM Table2 WHERE Table2.q1 = @q1
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION point2
    COMMIT TRAN
END CATCH

SAVE TRANSACTION point3
BEGIN TRY
    INSERT INTO Table3 VALUES (@t3,@fk1,@fk2)
    COMMIT TRAN
 END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION point3
    COMMIT TRAN
 END CATCH
END

But if Insert into Table2 fails, I get this:

(1 row(s) affected)

(0 row(s) affected)
Msg 628, Level 16, State 0, Procedure InsertInto, Line 26 (second BEGIN CATCH)
Cannot issue SAVE TRANSACTION when there is no active transaction.

How can I do this right?

Answer 1

SAVE TRAN requires transaction count > 0, thus you must have committed your transaction in the previous CATCH block. You have several options:

1) Replace your SAVE TRAN statement with the following (you can use the same savepoint name, however a rollback will rollback only to the last savepoint):

IF @@TRANCOUNT = 0
    BEGIN TRAN;
ELSE
    SAVE TRAN tran1;

2) In the CATCH block, add a BEGIN TRAN after the COMMIT TRAN

BEGIN CATCH
    ROLLBACK TRANSACTION point1
    COMMIT TRAN
    BEGIN TRAN
END CATCH

3) Remove all the COMMIT TRAN inside the CATCH blocks, and do a single COMMIT at the end.