Save Points in SQL Stored Procedures

CREATE TABLE #TestTable


(

Col1 INT,

Col2 VARCHAR(2),

Col3 VARCHAR(2)

)

GO

BEGIN TRANSACTION

INSERT INTO #TestTable

VALUES (1,'a','b');

SAVE TRANSACTION Sav1

GO

BEGIN TRANSACTION

UPDATE #TestTable SET Col2 = 'c'

WHERE Col1 = 1;

COMMIT TRANSACTION

ROLLBACK TRANSACTION Sav1;

COMMIT TRANSACTION

Savepoints offer a mechanism to roll back portions of transactions. You create a savepoint using the SAVE TRANSACTION savepoint_name statement. Later, you execute a ROLLBACK TRANSACTION savepoint_name statement to roll back to the savepoint instead of rolling back to the start of the transaction