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've been searching and only found comparisons between BEGIN and SAVE transaction, I did not understand why they are using them together and for what reason, I asked and no one knew how to respond! :
You do not have the complete procedure code, but if that's all, SAVE is unused in your case.
SAVE TRANSACTION saves a point in the code block to return if the transaction is canceled ( ROLLBACK ), that is, it cancels what was executed from that point, not the entire transaction.
A practical example would be the following:
A given process uses 2 procedures to complete:
"StoredProcedure1" and "StoredProcedure2".
StoredProcedure1 starts a transaction with BEGIN TRANSACTION and at some point executes StoredProcedure2. Imagine that in your stream, if an error occurs in StoredProcedure2, you want to make ROLLBACK only of what was run 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 here, where I have adapted this example: link