SQL Server nested transaction try...catch 处理模板

原文： http://rusanu.com/2009/06/11/exception-handling-and-nested-transactions/
如下， @trancount 用来定义外部存储过程在调用[usp_my_procedure_name]时有没有开启transaction, 如果没有，就在存储过程内部begin； 如果有，就保存外部transaction的状态，在rollback的时候rollback到进入这个sp时候的transaction的状态。

create procedure [usp_my_procedure_name]
as
begin
	set nocount on;
	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