This is an example of using a transaction savepoint:
DROP TABLE Test1 GO CREATE TABLE Test1 ( Code CHAR(3) ) SET NOCOUNT ON BEGIN TRAN INSERT INTO Test1 VALUES('aaa') SELECT * FROM Test1 -- aaa SAVE TRAN P1 INSERT INTO Test1 VALUES('bbb') SELECT * FROM Test1 -- aaa,bbb SAVE TRAN P2 INSERT INTO Test1 VALUES('ccc') SELECT * FROM Test1 -- aaa,bbb,ccc SAVE TRAN P3 INSERT INTO Test1 VALUES('ddd') SELECT * FROM Test1 -- aaa,bbb,ccc,ddd ROLLBACK TRAN P2 SELECT * FROM Test1 -- aaa,bbb ROLLBACK TRAN P1 SELECT * FROM Test1 -- aaa INSERT INTO Test1 VALUES('xxx') SELECT * FROM Test1 -- aaa,xxx COMMIT TRAN SELECT * FROM Test1 -- aaa,xxx SET NOCOUNT OFF
Code ---- aaa Code ---- aaa bbb Code ---- aaa bbb ccc Code ---- aaa bbb ccc ddd Code ---- aaa bbb Code ---- aaa Code ---- aaa xxx Code ---- aaa xxx