MRR8Wqjg

1BEGIN TRAN
2UPDATE 1
3UPDATE 2
4UPDARE 3
5COMMIT TRAN
6	
7BEGIN TRAN
8    UPDATE 1
9    UPDATE 2
10    UPDARE 3
11if @error<>0
12    rollback tran
13    COMMIT TRAN
14	
15BEGIN TRAN
16INSERT INTO ttt VALUES  (GETDATE())
17	
18COMMIT TRAN
19	
20ROLLBACK tran
21	
22BEGIN TRAN
23UPDATE 1
24UPDATE 2 --error
25UPDATE 3
26COMMIT TRAN
27	
28BEGIN TRAN
29UPDATE 1
30UPDATE 2
31UPDATE 3
32IF @@ERROR <> 0
33    ROLLBACK TRAN
34ELSE
35    COMMIT TRAN
36	
37BEGIN TRY
38    BEGIN TRAN
39    UPDATE 1
40    UPDATE 2 --error
41    UPDATE 3
42    COMMIT TRAN
43END TRY
44BEGIN CATCH
45    ROLLBACK TRAN
46END CATCH
47	
48SET XACT_ABORT ON
49BEGIN TRAN
50UPDATE 1
51UPDATE 2 --error
52UPDATE 3
53COMMIT TRAN
54	
55create table test (id int primary key);
56	
57begin tran;
58insert into test (id) values (1);
59select * from test;
60	
61set xact_abort on;
62begin tran;
63insert into test (id) values (2);
64select * from test;
65insert into test (id) values (2); --error: Violation of PK ...
66select * from test;
67commit tran;
68	
69set xact_abort off;
70begin tran;
71insert into test (id) values (3);
72insert into test (id) values (3); -- error
73insert into test (id) values (4);
74commit tran;
75select * from test;
76	
77set xact_abort on;
78begin tran;
79insert into test (id) values (5);
80if not exists (select 1 from test where id = 0)
81    raiserror('Bad data', 16, 1);
82commit tran;
83select * from test;
84	
85-- вернули опцию в состояние по-умолчанию, если она была оставлена в состоянии ON
86set xact_abort off;
87	
88begin try
89    begin tran;
90    -- тут делаем что-то
91    commit tran;
92end try
93begin catch
94    rollback tran;
95end catch
96	
97create procedure dbo.SetUserInfo
98(
99    @uid uniqueidentifier = NULL,
100    @info xml
101)
102as
103begin try
104    set nocount, xact_abort on;
105
106    if @info is NULL or @info.exist('/User') = 0
107    begin
108        raiserror('No or bad data provided.', 16, 1);
109        return;
110    end;
111
112    begin transaction;
113
114    declare @inserted table (ID int not NULL);
115    declare @id int;
116
117    merge into dbo.Users t
118    using(
119        select
120            @uid,
121            @info.value('(/User/@FirstName)[1]', 'nvarchar(50)'),
122            @info.value('(/User/@LastName)[1]', 'nvarchar(50)')
123        ) s(UID, FirstName, LastName)
124    on t.UID = s.UID
125    when matched then
126        update
127        set t.FirstName = s.FirstName, t.LastName = s.LastName
128    when not matched then
129        insert (UID, FirstName, LastName)
130        values (s.UID, s.FirstName, s.LastName)
131    output inserted.ID into @inserted (ID)
132        ;
133
134    select @id = ID from @inserted;
135
136    merge into dbo.UserContacts t
137    using (
138        select @id, ct.ID, x.c.value('@Value', 'nvarchar(400)')
139        from @info.nodes('/User[1]/Contacts[1]/Contact') x(c)
140            join dbo.UserContactTypes ct on ct.Type = x.c.value('@Type', 'nvarchar(400)')
141    ) s (UserID, ContactTypeID, ContactInfo)
142    on t.UserID = s.UserID and t.ContactTypeID = s.ContactTypeID
143    when not matched by source then
144        delete
145    when matched then
146        update
147        set t.ContactInfo = s.ContactInfo
148    when not matched then
149        insert (UserID, ContactTypeID, ContactInfo)
150        values (s.UserID, s.ContactTypeID, s.ContactInfo)
151        ;
152
153    commit transaction;
154
155end try
156begin catch
157    declare
158        @errMsg nvarchar(4000) = error_message(),
159        @errLine int = error_line(),
160        @procName sysname = quotename(object_schema_name(@@procid)) + '.' + quotename(object_name(@@procid))
161        ;
162
163    if @@trancount > 0
164        rollback transaction;
165
166    raiserror('%s in %s at %d', 16, 1, @errMsg, @procName, @errLine);
167end catch
168GO
169	
170create procedure dbo.GetSalesData
171(
172    @dateFrom datetime,
173    @dateTo datetime
174)
175as
176begin try
177    set nocount on;
178
179    declare @userID int;
180    select @userID = UserID from #Session;
181
182    if @userID is NULL
183    begin
184        raiserror('Access denied.', 16, 1);
185        return;
186    end;
187
188    create table #Orders (OrderID int not NULL);
189    alter table #Orders add primary key (OrderID);
190
191    set transaction isolation level snapshot;
192    begin transaction;
193
194    insert into #Orders (OrderID)
195    select op.OrderID
196    from dbo.OrderPermissions(@userID) op
197        join dbo.Orders ord on ord.ID = op.OrderID
198    where op.[Permissions] > 0
199        and ord.[Date] >= @dateFrom and ord.[Date] < @dateTo
200
201    -- some check based on #Order and other data
202    if exists (select 1 from #Orders o join ... where ...)
203    begin
204        raiserror('Check fail.', 16, 1);
205        return;
206    end;
207
208    select ...
209    from dbo.Orders ord
210        join #Orders o on o.OrderID = ord.ID
211
212    select ...
213    from dbo.Invoices inv
214        join #Orders o on o.OrderID = inv.OrderID
215
216    select ...
217    from dbo.Shipment sh
218        join #Orders o on o.OrderID = sh.OrderID
219
220    commit transaction;
221
222end try
223begin catch
224    declare
225        @errMsg nvarchar(4000) = error_message(),
226        @errLine int = error_line(),
227        @procName sysname = quotename(object_schema_name(@@procid)) + '.' + quotename(object_name(@@procid))
228        ;
229
230    if xact_state() = 1
231        commit transaction;
232    else if xact_state() = -1
233        rollback transaction;
234
235    raiserror('%s in %s at %d', 16, 1, @errMsg, @procName, @errLine);
236end catch
237GO