好记性不如烂笔头。

关于存储过程调用带事务的存储过程错误累加的问题

--存储过程B:
create proc proc_B
@errors int out           --输出存储过程的错误号,如果存储过程没有错误,则@errors等于0
as
begin
    begin tran
        set @errors=0      --必须初始化@errors,否则不能进行错误累加
        --具体的TSQL语句实现
        set @errors+=@@ERROR          --错误累计
        if(@errors!=0)              
            begin
                rollback tran           --回滚事务的时候,存储过程并没有执行,因此并没有输出参数
            end
        else
            begin
                commit tran             --提交事务,存储过程并没有错误,因此输出参数为0
            end
end


go

--存储过程A调用带事务的存储过程B:
create proc proc_A
as
begin
    begin tran
        declare @errors int=0                   --错误累加变量,必须初始化=0            
        --存储过程A的具体TSQL语句
        set @errors+=@@ERROR

        declare @procErrors int                 --变量用于接收存储过程B的输出参数,不需要初始化等于0,由于没有初始化,默认值为null
        exec proc_B @procErrors output
        --@procErrors is null表示调用存储过程B失败,否则@procErrors=0,因为存储过程B没有错误是输出0的,
        --有错误的话,是没有输出参数的,因此失败的话 @procErrors is null ,
        --这就是为什么@procErrors不能初始化在原因
        if(@procErrors is null)                 
            begin
                set @errors+=1                  --出错了,设置错误数为大于0的数
            end

        if(@errors!=0)              
            begin
                rollback tran           
            end
        else
            begin
                commit tran             
            end
end

 

如果想事务执行失败后,又有返回信息,用下面的方式

ALTER PROCEDURE P_xxxx
    @ERROR_NUMBER INT OUT,              --错误号,返回0无错误
    @ERROR_MESSAGE NVARCHAR(max) OUT        --错误信息
AS
/*
declare @ERROR_NUMBER int,@ERROR_MESSAGE nvarchar(max)
exec P_xxxx @ERROR_NUMBER out,@ERROR_MESSAGE out
print @ERROR_NUMBER
print @ERROR_MESSAGE
*/
BEGIN
    SET NOCOUNT ON;
SET XACT_ABORT ON BEGIN TRAN BEGIN TRY SET @ERROR_NUMBER = 0 --逻辑代码
--自定义错误
SET @ERROR_NUMBER += 1 END TRY BEGIN CATCH --出错后,设置错误号及错误信息,用输出参数输出 SET @ERROR_NUMBER =ERROR_NUMBER()  SET @ERROR_MESSAGE =ERROR_MESSAGE() END CATCH
IF(@ERROR_NUMBER<>0)
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END END GO

说明:
1 、使用存储过程执行事物,需要开启XACT_ABORT参数(默认值为Off),将该参数设置为On,表示当执行事务时,如果出错,会将transcation设置为uncommittable状态,那么在语句块批处理结束后将回滚所有操作;如果该参数设置为Off,表示当执行事务时,如果出错,出错的语句将不会执行,其他正确的操作继续执行。

2、当SET NOCOUNT 为 ON 时,不返回计数(计数表示受 Transact-SQL 语句影响的行数,例如在Sql server查询分析器中执行一个delete操作后,下方窗口会提示(3)Rows Affected)。当 SET NOCOUNT 为 OFF 时,返回计数,我们应该在存储过程的头部加上SET NOCOUNT ON 这样的话,在退出存储过程的时候加上 SET NOCOUNT OFF这样的话,以达到优化存储过程的目的。

二、事务内设置保存点
  用户可以在事务内设置保存点或标记。保存点定义如果有条件地取消事务的一部分,事务可以返回的位置。如果将事务回滚到保存点,则必须(如果需要,使用更多的 Transact-SQL 语句和 COMMIT TRANSACTION 语句)继续完成事务,或者必须(通过将事务回滚到其起始点)完全取消事务。若要取消整个事务,请使用 ROLLBACK TRANSACTION transaction_name 格式。这将撤消事务的所有语句和过程。如:

Create Procedure  MyProcedure
AS
    Begin
           Set    NOCOUNT    ON;
           Set XACT_ABORT ON;           
           begin   tran  ok  --开始一个事务OK 
              delete  from  rxqz  where qz=   'rx015 ' --删除数据  
           save   tran  bcd   --保存一个事务点命名为bcd
              update  sz  set   name='李丽s' where name= '李丽'--修改数据
  
           if  @@error<>0  --判断修改数据有没有出错  
               begin --如果出错  
                      rollback   tran  bcd  -- 回滚事务到BCD 的还原点 
                   commit   tran  ok  --提交事务      
               end    
           else  --没有出错 
               commit  tran ok --提交事务  
     End