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

 

事務註意事項：事務里不能包含事務，也就是說當存儲過程調用存儲過程的時候，外層存儲過程有事務時，被調用的存儲過程不能寫事務，不然事務回滾不了。