EXEC语句失败后，SQL语句没有返回结果

Question

我有我的SQL脚本如下

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;

EXEC base.usp_load_fact_table @batch_load_control_code=@batch_load_control_code,@disable_output=1;
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;

由于我正在测试的PK违规场景，我的EXEC步骤预计会失败。下面的select语句将告诉我，我的场景是否按照预期失败了。但是我的结果窗口没有显示从选择的结果后得到EXEC步骤的失败。你能告诉我如何让选择返回行吗?提前谢谢。

Answer 1

正如Stu所建议的，我尝试添加try/catch块，它适用于我的场景。

BEGIN TRY
EXEC base.usp_load_fact_table @batch_load_control_code=@batch_load_control_code,@disable_output=1;
END TRY
BEGIN 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;
END CATCH