#setup
create table txnproctable (c1 int not null, c2 varchar(100))

# PROC
# 		BEGIN TRAN
#			SAVEPOINT
#  		ROLLBACK SAVEPOINT
#		COMMIT
create procedure txnproc1 as begin tran; insert into txnproctable values (1, 'abc'); save tran sp1; update txnproctable set c1 = c1 + 1;  rollback tran sp1; commit tran;
select @@trancount;
select * from txnproctable order by c1;
exec txnproc1;
select @@trancount;
select * from txnproctable order by c1;


# PROC
# 		BEGIN TRAN
#			SAVEPOINT
#  		ROLLBACK SAVEPOINT
#		ROLLBACK
drop procedure txnproc1;
create procedure txnproc1 as begin tran; insert into txnproctable values(2, 'xyz'); save tran sp1; delete from txnproctable; rollback tran sp1; rollback tran;
select @@trancount;
select * from txnproctable order by c1;
exec txnproc1;
select @@trancount;
select * from txnproctable order by c1;


# PROC
# 		BEGIN TRAN
#			SAVEPOINT
#  		ROLLBACK SAVEPOINT
# COMMIT
drop procedure txnproc1
create procedure txnproc1 as begin tran; insert into txnproctable values(3, 'dbd'); save tran sp1; update txnproctable set c1 = c1 + 1; rollback tran sp1;
select @@trancount
select * from txnproctable order by c1;
exec txnproc1;
select @@trancount
select * from txnproctable order by c1;
commit tran;
select @@trancount
select * from txnproctable order by c1;


# PROC
# 		BEGIN TRAN
#			SAVEPOINT
# ROLLBACK SAVEPOINT
# COMMIT
drop procedure txnproc1
create procedure txnproc1 as begin tran; insert into txnproctable values(4, 'sbd'); save tran sp1; update txnproctable set c1 = c1 + 1;
select @@trancount
select * from txnproctable order by c1;
exec txnproc1;
select @@trancount
select * from txnproctable order by c1;
rollback tran sp1;
select @@trancount
select * from txnproctable order by c1;
commit tran;
select @@trancount
select * from txnproctable order by c1;


# BEGIN TRAN 
# PROC
#		SAVEPOINT
# 		ROLLBACK SAVEPOINT
# COMMIT
begin tran;
select @@trancount
select * from txnproctable order by c1;
drop procedure txnproc1
create procedure txnproc1 as insert into txnproctable values(5, 'abc'); save tran sp1; update txnproctable set c1 = c1 + 1; rollback tran sp1;
select @@trancount
select * from txnproctable order by c1;
exec txnproc1;
select @@trancount
select * from txnproctable order by c1;
commit tran;
select @@trancount
select * from txnproctable order by c1;


# BEGIN TRAN 
# PROC
#		SAVEPOINT
# 		ROLLBACK SAVEPOINT
# ROLLBACK	
begin tran;
select @@trancount
select * from txnproctable order by c1;
drop procedure txnproc1
create procedure txnproc1 as insert into txnproctable values(6, 'abc'); save tran sp1; update txnproctable set c1 = c1 + 1; rollback tran sp1;
select @@trancount
select * from txnproctable order by c1;
exec txnproc1;
select @@trancount
select * from txnproctable order by c1;
commit tran;
select @@trancount
select * from txnproctable order by c1;

# BEGIN TRAN 
# PROC
#		SAVEPOINT
# 		ROLLBACK SAVEPOINT
#		COMMIT
begin tran;
select @@trancount
select * from txnproctable order by c1;
drop procedure txnproc1
create procedure txnproc1 as insert into txnproctable values(7, 'abc'); save tran sp1; update txnproctable set c1 = c1 + 1; rollback tran sp1; update txnproctable set c1 = c1 + 1; commit tran; 
select @@trancount
select * from txnproctable order by c1;
exec txnproc1;
select @@trancount
select * from txnproctable order by c1;

# BEGIN TRAN 
# PROC
#		SAVEPOINT
# 		ROLLBACK SAVEPOINT
#		ROLLBACK
begin tran;
select @@trancount
select * from txnproctable order by c1;
drop procedure txnproc1
create procedure txnproc1 as insert into txnproctable values(8, 'abc'); save tran sp1; update txnproctable set c1 = c1 + 1; rollback tran sp1; update txnproctable set c1 = c1 + 1; rollback tran; 
select @@trancount
select * from txnproctable order by c1;
exec txnproc1;
select @@trancount
select * from txnproctable order by c1;

# BEGIN TRAN
# START SAVEPOINT
# PROC
# ROLLBACK SAVEPOINT
# ROLLBACK
begin tran;
select @@trancount
select * from txnproctable order by c1;
save tran sp1;
select @@trancount
select * from txnproctable order by c1;
drop procedure txnproc1
create procedure txnproc1 as insert into txnproctable values(9, 'abc'); update txnproctable set c1 = c1 + 1;
select @@trancount
select * from txnproctable order by c1;
exec txnproc1;
rollback tran sp1;
select @@trancount
select * from txnproctable order by c1;
rollback tran;
select @@trancount
select * from txnproctable order by c1;

# BEGIN TRAN
# START SAVEPOINT
# PROC
# ROLLBACK SAVEPOINT
# ROLLBACK
begin tran;
select @@trancount
select * from txnproctable order by c1;
save tran sp1;
select @@trancount
select * from txnproctable order by c1;
drop procedure txnproc1
create procedure txnproc1 as insert into txnproctable values(10, 'abc'); update txnproctable set c1 = c1 + 1;
select @@trancount
select * from txnproctable order by c1;
exec txnproc1;
rollback tran sp1;
select @@trancount
select * from txnproctable order by c1;
commit tran;
select @@trancount
select * from txnproctable order by c1;

# BEGIN TRAN
# START SAVEPOINT
# PROC
# 		ROLLBACK SAVEPOINT
# ROLLBACK
begin tran;
select @@trancount
select * from txnproctable order by c1;
save tran sp1;
select @@trancount
select * from txnproctable order by c1;
drop procedure txnproc1
create procedure txnproc1 as insert into txnproctable values(11, 'abc'); rollback tran sp1; update txnproctable set c1 = c1 + 1;
select @@trancount
select * from txnproctable order by c1;
exec txnproc1;
commit tran;
select @@trancount
select * from txnproctable order by c1;

# PROC1
#		BEGIN TRAN
#		PROC2
#			PROC3
#				BEGIN TRAN
#				START SAVEPOINT
#				ROLLBACK SAVEPOINT
#			COMMIT
#	COMMIT
create procedure txnProc3 as begin tran; insert into txnproctable values (16, 'abc'); save tran sp1; update txnproctable set c1 = c1 + 1;  rollback tran sp1;
select @@trancount
select * from txnproctable order by c1;
create procedure txnProc2 as update txnproctable set c1 = c1 + 1; exec txnProc3; commit tran;
select @@trancount
select * from txnproctable order by c1;
drop procedure txnproc1
create procedure txnproc1 as delete from txnproctable; begin tran; exec txnProc2;
select @@trancount
select * from txnproctable order by c1;
exec txnproc1;
select @@trancount
select * from txnproctable order by c1;
commit tran;
select @@trancount
select * from txnproctable order by c1;

# PROC1
#		BEGIN TRAN
#		PROC2
#			PROC3
#				BEGIN TRAN
#				START SAVEPOINT
#				ROLLBACK SAVEPOINT
#			ROLLBACK TRAN
#	COMMIT
drop procedure txnproc3
create procedure txnProc3 as begin tran; insert into txnproctable values (20, 'abc'); save tran sp1; update txnproctable set c1 = c1 + 1;  rollback tran sp1;
select @@trancount
select * from txnproctable order by c1;
drop procedure txnproc2
create procedure txnProc2 as update txnproctable set c1 = c1 + 1; exec txnProc3; rollback tran;
select @@trancount
select * from txnproctable order by c1;
drop procedure txnproc1
create procedure txnproc1 as delete from txnproctable; begin tran; exec txnProc2;
select @@trancount
select * from txnproctable order by c1;
exec txnproc1;
select @@trancount
select * from txnproctable order by c1;

#cleanup
drop procedure txnproc3
drop procedure txnproc2
drop procedure txnproc1
drop table txnproctable