Nested transaction Rollback Example

Reference: Introduction to SQL Transaction (Transaction) usage and rollback instance

--Test table
CREATE TABLE T1
	(
	ID int identity (1,1),
	Code nvarchar) go

--Stored procedure
Create Procedure P_rollbacktest 
as 
begin
	SET NOCOUNT on
	declare @n int = 0 
	
	begin tran Outtrans
	INSERT into T1 (code) VALUES (' A ')-		-Save in table	

	begin try 
		begin tran Innertrans INSERT INTO 

		T1 (code) VALUES (' B ')	--Before savepoint, save Tran SavePoint in the table-			-Create transaction save point 
		
		insert INTO T1 (Code) VALUES (' C ') After c22/>--savepoint, discard
				
		Set @n = 10/0				--Exception
		
		commit tran Innertrans end 
	try 
	begin catch 
		Rol Lback Tran SavePoint--rollback			to savepoint 
		Tran Innertrans			--Note: There is an error with rollback TRAN, only commit Tran end
	Catch 
	
	insert INTO T1 (Code) VALUES (' D ')-		-Save in table		
	commit tran Outtrans end	
go 

Exec p_ Rollbacktest
Go 

select * from T1