存储过程中调用存储过程时，子事务回滚

存储过程执行失败时，回滚数据的问题

新：
推荐使用这种方案：Err的return之前添加raiserror(@errmsg, 16, 1)
加这个很重要，在批量执行时能出错中断后续逻辑，并且要加在Select返回的后面，确保平台执行存储过程方法正常接收返回

Err:
    If @TranStarted = 1 ROLLBACK TRAN
    Select @issuccess as issucess, @errmsg as errmsg
    raiserror(@errmsg, 16, 1)
    RETURN

原：
关键语句：set xact_abort on
当xact_abort选项为on时，SQL Server 在遇到错误时会终止执行并rollback整个事务。

重要：如果该事务中包含子事务，子事务也会回滚。

两个示例存储过程

存储过程 1 Proc_Test1

SET NOCOUNT ON
DECLARE @TranStarted bit = 0    
        ,@issuccess int = 0   
        ,@errmsg varchar(255) = ''  

--偿试开始事务    
IF (@@TRANCOUNT = 0)    
BEGIN    
    BEGIN TRANSACTION    
    SET @TranStarted = 1    
END 

insert into xxx
exec Proc_Test2

SELECT @errmsg = '成功！',@issuccess = 1

Ret:      
    If @TranStarted = 1 COMMIT TRAN        
    Select @issuccess as issucess, @errmsg as errmsg      
    RETURN

Err:       
    If @TranStarted = 1 ROLLBACK TRAN        
    Select @issuccess as issucess, @errmsg as errmsg      
    RETURN

存储过程2 Proc_Test2

SET NOCOUNT ON
DECLARE @TranStarted bit = 0    
        ,@issuccess int = 0   
        ,@errmsg varchar(255) = ''  

--偿试开始事务    
IF (@@TRANCOUNT = 0)    
BEGIN    
    BEGIN TRANSACTION    
    SET @TranStarted = 1    
END 

insert into xxx

-- 抛出异常
DECLARE @num INT,@temp INT=0
SET @num =2/@temp
IF @@ERROR <> 0 goto Err

SELECT @errmsg = '成功！',@issuccess = 1              

Ret:      
    If @TranStarted = 1 COMMIT TRAN        
    Select @issuccess as issucess, @errmsg as errmsg      
    RETURN

Err:       
    If @TranStarted = 1 ROLLBACK TRAN        
    Select @issuccess as issucess, @errmsg as errmsg      
    RETURN
end

测试结果

数据回滚失败主要原因

• 子存储过程中的 IF(@@TRANCOUNT = 0) 判断没有进入，@@TRANCOUNT的值为1，@TranStarted的值为0，即使goto Err后，依旧没有ROLLBACK TRAN

系统级别异常

执行存储过程1时，两个存储过程中的insert语句都会执行成功，而不会回滚数据
解决方法：在存储过程1的头部添加set xact_abort on后，数据即可回滚成功

根据错误严重性级别，有些可以回滚成功，比如此语法错误：select convert(nvarchar(20),getdate(),1205)

手动跳转到执行失败

将存储过程 2中的抛出异常片段

DECLARE @num INT,@temp INT=0
SET @num =2/@temp
IF @@ERROR <> 0 goto Err

修改为：

set @errmsg = 'test2测试出错'
goto Err

此时执行存储过程1，同样两个存储过程中的 insert语句都会执行成功，也不会回滚数据
解决方法：

1. 在存储过程1中获取存储过程2的执行结果，来判断是否需要回滚数据，需要手动 goto Err
2. 在存储过程1头部添加set xact_abort on

declare @tb table(issuccess int,errmsg nvarchar(255))
insert into @tb exec Proc_Test2
if 0=(select issuccess from @tb) 
begin
    select @issuccess= issuccess,@errmsg=errmsg from @tb
    goto Err
end
delete @tb

总结

在存储过程头部添加set xact_abort on后，SQL Server 在遇到错误后会终止执行并回滚数据
子存储过程中goto Err后需要在当前存储过程捕获失败操作进行ROLLBACK TRAN,因为子存储过程中不会ROLLBACK TRAN