sql-server - 从TRY…CATCH block 调用时，SQL Server存储过程的返回码为NULL。

Question

假设我有以下SQL Server存储过程:

CREATE PROC TestSP AS
select 'results selected' as column1
RAISERROR('this is an error',16,1)
--RETURN 3

如果我这样称呼它

DECLARE @ReturnCode int
EXEC @ReturnCode = TestSP
select @ReturnCode as ReturnCode

返回码将为-6。如果我用 ALTER PROC取消注释存储过程中的 --RETURN 3行，然后再次调用它，则RETURN 3将覆盖并导致返回码为3。

如果我从TRY ... CATCH块中调用过程，

DECLARE @ReturnCode int
BEGIN TRY
    EXEC @ReturnCode = TestSP
END TRY
BEGIN CATCH
    PRINT 'An error occurred.'
END CATCH
SELECT @ReturnCode as ReturnCode

返回代码将为NULL。

使用返回码( EXEC @ReturnCode = TestSP)是否与从TRY ... CATCH块内调用存储过程不兼容？

Answer 1

从TRY ... CATCH块调用返回码时，返回码为NULL的原因可能与batch abortion有关。

如您所说，Microsoft SQL Server为程序员提供了执行捕获存储过程的方法，该方法可以捕获返回代码。

EXEC @ReturnCode = YourStoredProc

但您也可以选择从TRY ... CATCH块中调用存储过程，如下所示

BEGIN TRY
    EXEC YourStoredProc
END TRY
BEGIN CATCH
    <handle error>
END CATCH

如果要从TRY ... CATCH块中调用存储过程，似乎没有必要使用 EXEC @ReturnCode = YourStoredProc调用存储过程，因为正如您所指出的那样，返回代码将为NULL。 (注意:如果您的代码中没有TRY ... CATCH或显式的'RETURN n'，则RAISERROR会将错误严重程度为11的返回代码设置为-1，对于12的错误严重性为12，对于13的错误严重性为-3请注意，严重性低于20不会停止执行代码。)

TRY.CATCH方法为您提供了更多选项和信息来处理错误。在CATCH块中，您可以使用以下系统功能(取自 TRY...CATCH documentation):

ERROR_NUMBER()返回错误的编号。

ERROR_SEVERITY()返回严重性。

ERROR_STATE()返回错误状态编号。

ERROR_PROCEDURE()返回发生错误的存储过程或触发器的名称。

ERROR_LINE()返回导致错误的例程中的行号。

ERROR_MESSAGE()返回错误消息的全文。文本包括为任何可替换参数提供的值，例如长度，对象名称或时间。

您可以使用以下代码测试这些功能的输出:

BEGIN TRY
    EXEC TestSP  --A stored proc fashioned to cause an error
END TRY
BEGIN CATCH
    SELECT
    'ERROR_NUMBER()', ERROR_NUMBER(),''
    UNION SELECT
    'ERROR_SEVERITY()',ERROR_SEVERITY(),''
    UNION SELECT
    'ERROR_STATE() ',ERROR_STATE(),''
    UNION SELECT
    'ERROR_PROCEDURE()',0,ERROR_PROCEDURE()
    UNION SELECT
    'ERROR_LINE()',ERROR_LINE(),''
    UNION SELECT
    'ERROR_MESSAGE()',0,ERROR_MESSAGE()
END CATCH

最后，应该注意的是，在从TRY ... CATCH块调用的存储过程中使用RAISERROR时，严重度<= 10甚至都不被认为是错误，并且不会导致调用代码中的CATCH块触发。严重等级11+将导致CATCH块触发。在大多数情况下，我们应该将严重性级别16与RAISERROR一起使用。