How can we roll-back to a specific point while executing a stored procedure?

SQL gives you an option to save the transaction at any point during the execution of the procedure. Whenever an exception occurs during the transaction it will be rolled back to the last save point/ transaction . We can have multiple save points in a single procedure/ transaction. Syntax to save transaction is as follows:

SAVE TRANSACTION Trans_SAVE_point_name

Below is sample of procedure for multiple save points

Create Table Employ(EMP_Id int, EMP_Name nvarchar(50))

go

alter Procedure usp_test_SaveTrans

as

Begin

DECLARE @v_Second_Point NCHAR(50)='Trans_Second_Point'

BEGIN TRANSACTION

INSERT INTO Employ VALUES ( 1, N'EMP1',200000)

SAVE TRANSACTION First_Point

print 'Save-point after the first Point created'

INSERT INTO Employ VALUES ( 2, N'EMP2',234000)

SAVE TRANSACTION @v_Second_Point

print 'Save-point after the Second Point created'

INSERT INTO Employ VALUES ( 3, N'EMP3',500098)

ROLLBACK TRANSACTION @v_Second_Point

print 'ROLLBACK TRANSACTION Second_Point done'

COMMIT

SELECT * FROM Employ

end