某公司的存储过程模板(摘抄自高大神的博客)

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
create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    set lock_timeout 1500;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;
 
        -- Do the actual work here
 
lbexit:
        if @trancount = 0
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;
 
        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch
end
go

 

posted on   万剑齐发  阅读(319)  评论(0编辑  收藏  举报
努力加载评论中...

点击右上角即可分享
微信分享提示