主页 / dba / 问题 / 185416
Accepted
irimias
Asked: 2017-09-09 05:30:46 +0800 CST

如果存储过程在 TRY / CATCH 内失败，则未设置输出参数

  • 772

在 SQL Server 2008 中（但也在 2014 年）。让我们考虑一个具有输出参数的过程。此过程可能会产生错误（将在以下示例中出现）。我注意到如果我们在TRY/CATCH块中调用过程，输出参数的行为是不一样的。

例子：

create procedure test_output @result varchar(10) output
as
begin
    set @result = 'hello'
    raiserror('This is an error', 16,1)
    set @result = 'error'
end

如果我们以简单的方式启动该程序：

declare @res1 varchar(10)
exec test_output @result = @res1 out
print 'Result is: '+ isnull(@res1, 'empty')

我们得到（我很好）：

Msg 50000, Level 16, State 1, Procedure test_output, Line 7 [Batch Start Line 12]
这是一个错误
结果是：错误

如果过程现在在 try/catch 块中：

declare @res2 varchar(10)
declare @error_message varchar(max)
begin try
    exec test_output @result = @res2 out
end try
begin catch
    set @error_message = error_message()
    raiserror(@error_message, 16,1)
    print 'Result is: '+ isnull(@res2, 'empty')
end catch

我们得到（我很沮丧）：

消息 50000，级别 16，状态 1，第 28 行
这是一个错误
结果是：空

错误消息正常，但输出参数现在为NULL。如果在TRY...CATCH上下文中，执行在 之后立即停止RAISERROR，我会期望输出值设置为hello。

为什么会这样？

sql-server t-sql

1 个回答

  1. Best Answer

    您的测试设置有了一个良好的开端，但它缺少一些导致您误解实际发生的事情的东西。如果您在PRINT存储过程的开头、中间和结尾添加语句，那么额外的输出将使这里发生的事情更清楚。例如：

    USE [tempdb];
GO
CREATE PROCEDURE test_output
(
  @Result VARCHAR(10) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;

    SET @Result = 'hello';

    PRINT 1;
    RAISERROR('This is an error', 16, 1);

    PRINT 2;
    SET @Result = 'error';

    PRINT 3;
END;
GO

    您的第一个测试查询的输出是：

    1
Msg 50000, Level 16, State 1, Procedure test_output, Line xxxx [Batch Start Line yyyyy]
This is an error
2
3
Result is: error

    无论如何，这可能是您所期望的。但是，第二个测试查询的输出是：

    1
Msg 50000, Level 16, State 1, Line xxxxx
This is an error
Result is: empty

    那是相当不同的。我们现在可以看到，在TRY...CATCH构造中，执行在被调用时立即RAISERROR停止（即，它变成了批处理中止事件）。另一方面，在构造中RAISERROR未调用时不会立即停止执行。TRY...CATCH

    但是，正如您在对问题的更新中指出的那样，这并不能解释为什么该OUTPUT参数未设置为hello. 这是由于正常存储过程行为的意图是不反映部分执行（由于批处理中止错误）。这在以下博客文章中进行了讨论：

    TSQL 基础 II – 参数传递语义

    含义：即使存储过程确实执行了将变量设置为 的步骤hello，但RAISERROR现在在TRY...CATCH构造中调用时会出现批处理中止错误，并且存储过程在中止时不会反映对参数的任何更改。

    这种行为也是以下解释的核心：

    为什么 TVP 必须是 READONLY，其他类型的参数为什么不能是 READONLY

    • 6

相关问题