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.
----------------------------------------------------------
BEGIN TRAN
--------------------Processing 1--------
SAVE TRANSACTION sp1
Insert into tempPK select 111
Select 1/0
if @@Error<>0
rollback Tran sp1
--------------------Processing 2--------
SAVE TRANSACTION sp2
Insert into tempPK select 211
--Select 1/0
if @@Error<>0
rollback Tran sp2
--------------------Processing 3--------
SAVE TRANSACTION sp3
Insert into tempPK select 311
--Select 1/0
if @@Error<>0
rollback Tran sp3
commit tran
--------------------------------
OutPut:
Proessing 2 and 3 commited
Proessing 1 will be rollback
