SQL中EXEC语句执行失败后后续SELECT无返回结果解决方法

SQL Server默认处理逻辑里，碰到主键冲突这类运行时错误（严重性级别14），如果没做异常拦截，会直接终止当前整个批的所有后续执行。你写在EXEC后面的校验SELECT根本没跑到，自然不会出结果。

用TRY...CATCH块把可能报错的存储过程执行逻辑包起来，捕获到错误后不让它中断整个批流程，后面的校验语句就能正常执行返回结果。

修改后的完整脚本 #

DECLARE @batch_load_key INT;

SELECT  @batch_load_key=batch_load_key
FROM load.batch_load
WHERE batch_load_name = N'xxxx';

UPDATE load.batch_load_partition_control SET is_batch_load_partition_processed = 0
WHERE batch_load_control_key IN
(
SELECT batch_load_control_key
FROM load.batch_load_control
WHERE batch_load_key = @batch_load_key
);

UPDATE load.batch_load_control SET is_batch_load_processed = 0
WHERE batch_load_key = @batch_load_key;

SELECT  @batch_load_key=batch_load_key
FROM load.batch_load
WHERE batch_load_name = N'xxxx';

DECLARE @batch_load_control_code NVARCHAR(100);

SELECT @batch_load_control_code = batch_load_control_code
FROM load.batch_load_control
WHERE batch_load_key = @batch_load_key;

-- 用TRY块包裹可能报错的存储过程执行
BEGIN TRY
    EXEC base.usp_load_fact_table @batch_load_control_code=@batch_load_control_code,@disable_output=1;
END TRY
-- 捕获异常，可按需记录错误信息，也可留空仅拦截错误避免批终止
BEGIN CATCH
    -- 可选：打印错误信息确认命中预期的PK冲突场景
    PRINT '捕获到错误：' + ERROR_MESSAGE();
END CATCH

-- 后续校验语句不受报错影响，会正常执行返回结果
SELECT * from
load.batch_load_partition_control WHERE batch_load_control_key IN
(
SELECT batch_load_control_key
FROM load.batch_load_control
WHERE batch_load_key in (SELECT batch_load_key
FROM load.batch_load
WHERE batch_load_name = N'xxxx')
) AND is_batch_load_partition_processed = 1;

• 就算会话开了XACT_ABORT = ON（很多ETL脚本默认开这个配置），TRY...CATCH也能正常捕获主键冲突这类错误，不需要特意关闭该配置。
• 不建议直接用SET XACT_ABORT OFF强行让语句往下跑，这种方式会让事务状态不可控，还容易漏掉其他非预期的错误，用异常捕获是最稳妥的方案。
• 如果怕吞掉非预期的错误，可以在CATCH块里加判断，通过ERROR_NUMBER()识别错误号：主键冲突错误号是2627，唯一约束冲突是2601，只捕获你预期的PK冲突即可，其他错误直接抛出。

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