sql-server - 在 TSQL 上尝试并捕捉 - 捕捉未捕捉

Question

我有一个似乎没有正确记录错误的存储过程。

代码出错，但 catch 块似乎没有生效。

try 块相当长 - 但错误部分很简单，并且正好在最后，所以我已经精确地说明了这一点。

BEGIN TRY 
insert into tbl_X
select * from #temp_tbl_Y

RETURN 1
END TRY

BEGIN CATCH
    Insert Into ExtractsErrorLog
    SELECT 
    getdate() as ErrorDate 
    ,object_name(@@procid) as ProcedureName
    ,ERROR_NUMBER() as ErrorNumber
    ,ERROR_LINE() as ErrorLine
    ,ERROR_MESSAGE() as ErrorMessage
    ;
DECLARE @errormessage as varchar(max);
DECLARE @errorseverity as int;
DECLARE @errorstate as int;

set @errormessage = ERROR_MESSAGE();
set @errorseverity = ERROR_SEVERITY();
set @errorstate = ERROR_STATE();

 RAISERROR (@errormessage,
            @errorseverity,
            @errorstate
               );


END CATCH;

proc 失败的错误是我们的老 friend
“列名或提供的值数量与表定义不匹配。”
我已经修复了这个错误 - 这是一个愚蠢的懒惰错误 - 但我很困惑为什么我的错误记录过程似乎没有工作 - 没有行被插入到我的 ExtractsErrorLog 表中。

Answer 1

TSQL的TRY...CATCH没有捕捉到那个错误。此错误属于 CATCH 未处理的“编译/重新编译”类型错误。阻止“在同一执行级别内”。

来自 MSDN :

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

• Compile errors, such as syntax errors, that prevent a batch from running.

• Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution

...

You can use TRY…CATCH to handle errors that occur during compilation or statement-level recompilation by executing the error-generating code in a separate batch within the TRY block. For example, you do this by placing the code in a stored procedure or by executing a dynamic Transact-SQL statement using sp_executesql. This allows TRY…CATCH to catch the error at a higher level of execution than the error occurrence. For example, the following code shows a stored procedure that generates an object name resolution error. The batch that contains the TRY…CATCH construct is executing at a higher level than the stored procedure; and the error, which occurs at a lower level, is caught.

我在 TRY...CATCH 中创建事务的脚本遇到了类似的问题。那会 ROLLBACK如果交易失败。事务中的一条语句抛出了同样的错误并导致事务永远不会被关闭，如 CATCH从未进入。

正如 MSDN 文章中提到的，一种替代方法是从您的 INSERT 中创建一个存储过程。语句，然后在你的 try/catch 中调用它。如果 sproc 错误，您将在尝试创建它时捕获编译错误。如果表定义稍后更改以使 sproc 无效，则 TRY...CATCH将为您捕获异常。

如果您希望所有内容都包含在一个脚本中，您可以将其设为 temporary stored procedure ，但是您将需要在创建 sproc 时处理编译错误。它不漂亮，但它会起作用:

-- Creating error sproc to re-use code
CREATE PROCEDURE #HandleError AS
    Insert Into ExtractsErrorLog
    SELECT  GETDATE() as ErrorDate 
            ,object_name(@@procid) as ProcedureName
            ,ERROR_NUMBER() as ErrorNumber
            ,ERROR_LINE() as ErrorLine
            ,ERROR_MESSAGE() as ErrorMessage;

    DECLARE @errormessage as varchar(max);
    DECLARE @errorseverity as int;
    DECLARE @errorstate as int;

    set @errormessage = ERROR_MESSAGE();
    set @errorseverity = ERROR_SEVERITY();
    set @errorstate = ERROR_STATE();

    RAISERROR ( @errormessage,
                @errorseverity,
                @errorstate);
GO

-- Create a stored procedure of our INSERT and catch any compilation errors
CREATE PROCEDURE #TEST AS
    insert into tbl_X
    select * from #temp_tbl_Y
GO
IF (@@ERROR <> 0) BEGIN
    exec #HandleError
    -- If there was an error creating the sprocs, don't continue to the next batch
    RETURN
END

-- If compilation succeeded, then run the sproc
BEGIN TRY 
    exec #TEST
    RETURN
END TRY
BEGIN CATCH
    exec #HandleError
END CATCH;