Sqlserver 事务处理模板

use studb
go
/****** object: storedprocedure [dbo].[proc_live_send_answer_v4] script date: 06/20/2017 14:44:26 ******
/
set ansi_nulls on
go
set quoted_identifier on
go
-- =============================================
-- author: yj
-- create date: 2017-05-11
-- description: 事务
-- =============================================
alter procedure [dbo].[proc_name]
@answer_id varchar(50),
@answer varchar(10),
@uid bigint,
@roomid int=0 out,
@count int=0 out
as
begin
set nocount on;
if(charindex(':',@answer_id,1)=0)
return;

set @answer=replace(@answer,',','|');

declare @activity_no int;
declare @quest_order tinyint;
set @activity_no = substring(@answer_id,1,charindex(':',@answer_id,1)-1);
set @quest_order=substring(@answer_id,charindex(':',@answer_id,1)+1,len(@answer_id));

--尽量缩短事务占用时间
begin try
begin tran

insert......a
update......b
delete......c

commit tran
select 1 as res,'' as remark

end try
begin catch
if (@@trancount > 0)
rollback;
declare @errmessage nvarchar(1024) = error_message(),
@errseverity int = error_severity(),
@errstate int = error_state();
raiserror(@errmessage,@errseverity,@errstate);--抛出错误

select 0 as res,'提交失败' as remark
end catch

end

 

事务注意事项：事务里不能包含事务，也就是说当存储过程调用存储过程的时候，外层存储过程有事务时，被调用的存储过程不能写事务，不然事务回滚不了。