1USE StuDB 2GO 3/****** Object: StoredProcedure [dbo].[proc_live_send_answer_v4] Script Date: 06/20/2017 14:44:26 ******/ 4SET ANSI_NULLS ON 5GO 6SET QUOTED_IDENTIFIER ON 7GO 8-- ============================================= 9-- Author: YJ 10-- Create date: 2017-05-11 11-- Description: 事务 12-- ============================================= 13ALTER PROCEDURE [dbo].[proc_name] 14@answer_id varchar(50), 15@answer varchar(10), 16@uid bigint, 17@roomid int=0 out, 18@count int=0 out 19AS 20begin 21set nocount on; 22if(charindex(':',@answer_id,1)=0) 23return; 24 25set @answer=replace(@answer,',','|'); 26 27declare @Activity_No int; 28declare @quest_order tinyint; 29set @Activity_No = substring(@answer_id,1,charindex(':',@answer_id,1)-1); 30set @quest_order=substring(@answer_id,charindex(':',@answer_id,1)+1,len(@answer_id)); 31 32--尽量缩短事务占用时间 33begin try 34begin tran 35 36insert......a 37update......b 38delete......c 39 40commit tran 41select 1 as res,'' as remark 42 43end try 44begin catch 45if (@@trancount > 0) 46rollback; 47declare @ErrMESSAGE nvarchar(1024) = error_message(), 48@ERRSEVERITY int = error_severity(), 49@ERRSTATE int = error_state(); 50raiserror(@ErrMESSAGE,@ERRSEVERITY,@ERRSTATE);--抛出错误 51 52select 0 as res,'提交失败' as remark 53end catch 54 55end
事务注意事项：事务里不能包含事务，也就是说当存储过程调用存储过程的时候，外层存储过程有事务时，被调用的存储过程不能写事务，不然事务回滚不了。