1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
create procedure [dbo].[SK_upd_AppealCode]
@productid int,
@appealcode varchar(20)
as
begin
declare @err_message varchar(2000)
declare @err_occurred bit
set @err_message = ''
set @err_occurred = 0
set nocount on
begin tran upd_appealcode
update product set appealcode = @appealcode where productid = @productid
if @@error > 0 begin
set @err_message = 'update failed for table [Product]'
set @err_occurred = 0 -- T:1 F:0
goto err_handler
end
update TR_Product_TributeType set appealcode = @appealcode where productid = @productid
if @@error > 0 begin
set @err_message = 'update failed for table [TR_Product_TributeType]'
set @err_occurred = 0 -- T:1 F:0
goto err_handler
end
commit tran upd_appealcode
set nocount off
end
return
err_handler:
if (@err_message='') begin
set @err_message = 'Unknown error occurred'
end
rollback tran upd_appealcode
raiserror(@err_message,16,1)
return
GO
Update with transaction and error handling
This post is licensed under CC BY 4.0 by the author.