SQL SERVER 2000 嵌套 存储过程 事务 回滚_server sql server 多重嵌套存储过程怎么处理事务

/*当一段大的SQL语句中调用了多个存储过程，被调用的存储过程中又调用了多个存储过程，如此经过多次嵌套调用后，当遇到如重复主键、或是不满足业务逻辑时需要回滚整个事务，则可以使用下面的模式进行处理。也可以不进行raiserror进行直接计数。当SET XACT_ABORT ON后如果执行出错会自动回滚到最外层事务，但如果是业务逻辑出错则需要进行手动计数和手动回滚，即下面的DECLARE @ErrorSave INT SET @ErrorSave = 0|EXECUTE @ErrorSave = TTransProc @PriKey,@CharCol/*调用存储过程*/|SET @ErrorSave = @ErrorSave + @@ERROR|RETURN @ErrorSave*/

/*创建测试表*/
CREATE TABLE TestTrans (Cola int NOT NULL ,Colb char(3) NOT NULL,constraint PK_TestTrans primary key (Cola)) 
GO
/*创建存储过程*/
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
/*存储过程3*/
CREATE PROCEDURE TTransProc @PriKey INT, @CharCol CHAR(3)
AS 
DECLARE @ErrorSave INT SET @ErrorSave = 0
INSERT INTO TestTrans VALUES (@PriKey + 2, @CharCol) 
IF(@PriKey = 0) BEGIN
RAISERROR('TTransProc User error',16,1)
SET @ErrorSave = @ErrorSave + @@ERROR
END
RETURN @ErrorSave
GO
/*存储过程2*/
CREATE PROCEDURE STransProc @PriKey INT, @CharCol CHAR(3)
AS 
DECLARE @ErrorSave INT SET @ErrorSave = 0
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol) 
EXECUTE @ErrorSave = TTransProc @PriKey,@CharCol/*调用存储过程3*/
IF(@PriKey = 6) BEGIN
RAISERROR('STransProc User error',16,1)
SET @ErrorSave = @ErrorSave + @@ERROR
END
RETURN @ErrorSave
GO
/*存储过程1*/
CREATE PROCEDURE FTransProc @PriKey INT, @CharCol CHAR(3)
AS 
DECLARE @ErrorSave INT SET @ErrorSave = 0
INSERT INTO TestTrans VALUES (@PriKey, @CharCol) 
EXECUTE @ErrorSave = STransProc @PriKey,@CharCol/*调用存储过程2*/
IF(@PriKey = 11) BEGIN
RAISERROR('FTransProc User error',16,1)
SET @ErrorSave = @ErrorSave + @@ERROR
END
RETURN @ErrorSave
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
---------------------测试----------------------
/*设置此标记当执行过程中出错则自动回滚*/
SET XACT_ABORT ON
/*此变量用来保存用户自定义的错误，如当不满足业务要求时，使用RAISERROR抛出错误，然后进行计数，以便外层事务进行回滚*/
DECLARE @ErrorSave INT SET @ErrorSave = 0
BEGIN TRANSACTION OutOfProc
PRINT 'OA:@@ERROR='+ CONVERT(varchar,@@ERROR)+'|@ErrorSave='+ CONVERT(varchar,@ErrorSave)
EXECUTE @ErrorSave = FTransProc 6,'aaa'
PRINT 'OB:@@ERROR='+ CONVERT(varchar,@@ERROR)+'|@ErrorSave='+ CONVERT(varchar,@ErrorSave)
SET @ErrorSave = @ErrorSave + @@ERROR
IF (@ErrorSave = 0) BEGIN
COMMIT TRANSACTION OutOfProc 
END ELSE BEGIN
ROLLBACK TRANSACTION OutOfProc 
END
GO
-------------------------------------------
--SELECT * FROM testTrans
--DELETE FROM testTrans