Begin Transaction and Save Transaction SQL Server

Question

I have a Storedprocedure on a system I’m working on and I have the following code

begin transaction;
save transaction Integration_SP;
-- CODIGO
commit transaction;

I have been searching and only found comparisons between BEGIN and SAVE transaction, I do not understand why here they are using them together and what the reason, I asked and nobody knew answer! :(

Answer 1

You don’t have the full code of Procedure, but if that’s all, the SAVE is of no use in your case.

SAVE TRANSACTION saves a point in the code block to return if the transaction is canceled (ROLLBACK), that is, cancels what was executed from that point, and not the entire transaction.

A practical example would be the following:

A certain process uses 2 procedures to be completed:
"Storedprocedure1" and "Storedprocedure2".

Storedprocedure1 starts a transaction with BEGIN TRANSACTION and at some point, runs Storedprocedure2. Imagine that in your stream, if an error occurs in Storedprocedure2, you want to do the ROLLBACK only of what was executed in Storedprocedure2. In this case, you save a checkpoint in Storedprocedure2, so that it is possible, within the same transaction, to ROLLBACK only of that checkpoint. Example:

CREATE PROCEDURE StoredProcedure1 
 ....
 BEGIN TRANSACTION
 ....

 EXEC StoredProcedure2
 ....
 COMMIT
GO

And the second Procedure

CREATE PROCEDURE StoredProcedure2

   -- Verifica se já existe uma transação aberta e salva a informação e o *checkpoint* se necessário
    DECLARE @TranCounter INT;  
    SET @TranCounter = @@TRANCOUNT;

     IF @TranCounter > 0  
        -- se já existir uma transação, salva um checkpoint
        SAVE TRANSACTION Procedure2Checkpoint;  
    ELSE  
        -- senão inicia uma nova
        BEGIN TRANSACTION; 

    ....
    -- em caso de erro, tratado com TRY por exemplo:
      IF @TranCounter = 0  
           -- transação iniciada aqui, rollback de tudo
            ROLLBACK TRANSACTION;  
      ELSE  
            -- transação já iniciada antes, em StoreProcedure1, roolback só do que foi executado em StoredProcedure2, a partir do SAVE
            ROLLBACK TRANSACTION Procedure2Checkpoint; 

GO

See more here, where I adapted this example: https://docs.microsoft.com/pt-br/sql/t-sql/language-elements/save-transaction-transact-sql?view=sql-server-2017