BnsM0r74

1begin transaction
2
3set nocount on
4	CREATE TABLE TransactionTest (
5		[ID] [int] IDENTITY(1,1) NOT NULL,
6		[Value] [nvarchar](max) NULL)
7	
8
9	insert into TransactionTest Values ('Test 1');
10	
11	begin transaction
12	
13	    
14		select * from TransactionTest
15
16		
17
18		insert into TransactionTest Values ('Test 2');
19
20		IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('ProcedureTest') )
21		begin
22		  select 'procedure does not exist'
23
24
25
26		end 
27
28		go
29
30			create procedure ProcedureTest as
31			begin 
32			 select 'Procedure Test'
33			end 
34      
35	    go
36	
37
38
39		exec ProcedureTest
40
41
42	commit transaction 
43	--rollback transaction --if we rollback here it will rollback both transactions, 
44		--and it will result in the later rollback to be an error
45		--Better to do error with  RAISERROR (@msg, 16, 1 ) which will trigger a try catch if you have one and you can rollback in a catch at the bottom
46
47	select * from TransactionTest
48	exec ProcedureTest
49
50rollback transaction 
51
52print 'Errors after this message should be expected'
53--should result in invalid object
54exec ProcedureTest
55
56--should result in invalid object
57select * from TransactionTest
58
59
60--drop statements in case your rollback don't work while you test.
61--drop table  transactionTEst
62--drop procedure proceduretest