SQL Server工作存储过程捕错后INSERT EXEC调用报事务无法提交错误

这个报错是SQL Server的**不可提交事务（Doomed Transaction）**特性结合INSERT ... EXEC的隐式事务机制触发的：

• 当你使用INSERT ... EXEC语法执行存储过程并写入结果时，SQL Server会自动开启一个隐式事务来保证读写操作的原子性
• 你在工作存储过程中触发的类型转换错误属于「严重性≥16且会影响事务状态」的错误，即便被内层TRY/CATCH捕获，也会将当前事务标记为不可提交状态（XACT_STATE() = -1）
• 不可提交状态下的事务不允许执行任何写操作，因此后续要把存储过程返回的结果写入表/表变量时就会触发报错
• 单纯执行EXEC不做写入操作时，不会触发隐式事务，因此即便工作存储过程内部有被捕获的错误，也不会影响外层执行

以下三种方案均可解决该问题，可根据业务场景选择： #

方案1：工作存储过程的CATCH块中处理事务状态 #

在所有工作存储过程的CATCH块中添加事务状态判断和回滚逻辑，清理不可提交的事务状态：

CREATE PROCEDURE bugchase.worker_2
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @result INT;

    BEGIN TRY
        SET @result = CAST('ABCD' AS FLOAT);
    END TRY
    BEGIN CATCH
        -- 新增：如果当前事务处于不可提交状态，直接回滚清理状态
        IF XACT_STATE() = -1
            ROLLBACK TRANSACTION;
    END CATCH;

    SELECT 'Result Worker 2';
END;
GO

方案2：改用临时表传递结果，避免使用INSERT ... EXEC #

在调度存储过程中先创建临时表，工作存储过程直接往临时表写入结果，绕开INSERT ... EXEC的隐式事务机制：

-- 调度存储过程修改示例
CREATE PROC bugchase.runner_selectAndInsert
AS
BEGIN
    SET NOCOUNT ON;
    CREATE TABLE #result (value NVARCHAR(64));

    PRINT 'Select&Insert: Worker 1 start';
    EXEC bugchase.worker_1; -- 工作存储过程内部直接INSERT INTO #result写入结果
    PRINT 'Select&Insert: Worker 1 end';

    PRINT 'Select&Insert: Worker 2 start';
    EXEC bugchase.worker_2;
    PRINT 'Select&Insert: Worker 2 end';

    SELECT * FROM #result;
    DROP TABLE #result;
END;
GO

方案3：在调度存储过程的INSERT ... EXEC外层加TRY/CATCH #

在调用工作存储过程的位置外层再加一层异常捕获，处理事务状态后继续执行：

-- 调度存储过程中调用worker_2的部分修改
PRINT 'Select&Insert: Worker 2 start';
BEGIN TRY
    INSERT INTO @result (value)
    EXEC bugchase.worker_2;
END TRY
BEGIN CATCH
    IF XACT_STATE() = -1
        ROLLBACK TRANSACTION;
    -- 可选：将错误信息写入结果表记录异常
    INSERT INTO @result (value) VALUES ('Worker 2执行异常');
END CATCH;
PRINT 'Select&Insert: Worker 2 end';

内容的提问来源于stack exchange，提问作者Robert Dehmel