Why does a SAVE TRAN outside of a CURSOR loop not work, but inside does?

Question

Why, when my SAVE TRAN SavePoint is at location (A) do I get these errors on the third instance of falling into the CATCH and beyond...

"Msg 6401, Level 16, State 1 ... Cannot roll back SavePoint. No transaction or savepoint of that name was found."

...but I do not get such errors when SAVE TRAN SavePoint; is at location (B)?

I can only imagine it's something to do with the context of the cursor loop, but I don't understand why.

-- Premable stuff, opening cursor, etc. here...

BEGIN TRAN

SAVE TRAN SavePoint;  -- (A)

WHILE @@FETCH_STATUS = 0
BEGIN;

  -- SAVE TRAN SavePoint;  -- (B)

  BEGIN TRY

    -- UPDATE statement here which may fail

    SAVE TRAN SavePoint;

  END TRY
  BEGIN CATCH

    ROLLBACK TRAN SavePoint;

  END CATCH;

  FETCH NEXT FROM Cursor INTO @X, @Y, @Z;

END;

-- CLOSE & DEALLOCATE stuff here

IF @DryRun = 1
  ROLLBACK TRAN;
ELSE
  COMMIT TRAN;

Answer 1

When you roll back to a save point, that save point is used up.

In position A, you only set it once, so it can only be used once.

You are allowed to set a save point with the same name more than once.

When you do that in position B, you ensure it is available on every iteration of the loop.

The behaviour is a little weird, and save points aren't widely used for many reasons.

This prints your error:

BEGIN TRANSACTION;
    SAVE TRANSACTION SP1; -- only set once
    ROLLBACK TRANSACTION SP1;
    PRINT 'Success!'

    ROLLBACK TRANSACTION SP1;
    PRINT 'Less success'
COMMIT TRANSACTION;

This does not cause an error:

BEGIN TRANSACTION;
    SAVE TRANSACTION SP1;
    ROLLBACK TRANSACTION SP1;
    PRINT 'Success!'

    SAVE TRANSACTION SP1;
    ROLLBACK TRANSACTION SP1;
    PRINT 'More success!'
COMMIT TRANSACTION;

Somewhat curiously, neither does this:

BEGIN TRANSACTION;
    SAVE TRANSACTION SP1;
    SAVE TRANSACTION SP1;
    ROLLBACK TRANSACTION SP1;
    PRINT 'Success!'

    ROLLBACK TRANSACTION SP1;
    PRINT 'More success!'
COMMIT TRANSACTION;