在TSQL中使用Try和Catch - catch语句无法捕获异常

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;

这个存储过程失败的错误是我们老朋友“提供的值的列名或编号与表定义不匹配”。 我已经修复了这个错误——这是一个愚蠢的懒惰错误——但我很困惑为什么我的错误日志处理似乎没有起作用——没有将任何行插入到我的ExtractsErrorLog表中。

- DanBennett

---

这是一个编译时错误，请参见：https://dev59.com/xFrUa4cB1Zd3GeqPoObM - Ivan Golović

你能发布 ExtractsErrorLog 表的定义吗？ - Lamak

谢谢Ivan - 这很有道理 - 那个线程也提供了一个解决方法。很抱歉错过了并重新发布。我读到了catch无法捕获编译错误，但我没有意识到这将是一个编译错误。 有人知道是否有在线指南可以理解在编译/重新编译时发生的错误吗？我现在可以看到为什么会出现这种情况，我想缺少表等等。但我想要确定，我没有找到太多关于此的详细信息。 - DanBennett

@DanBennett，你解决这个问题的方法了吗？我预计会出现这个错误，并希望使用TRY|CATCH来运行不同的逻辑 - 解决方法只是将错误移动到内部SP调用。 - ColinMac

嗨@ColinMac - 请查看Ivan G的消息中的链接，它指向一些资源，其中一些指向选项。最终我们没有追求这个方案，因为我们认为捕获错误的选项增加了人工时间和代码复杂性，这超过了好处。你的情况可能不同。 - DanBennett

如果内部的sp调用只是一个简单的选择语句，那么编译时错误就不会发生。然后顶层SP运行，并在意识到SP选择语句具有更改的表定义并且捕获起作用时出现错误。 - ColinMac

Answer 1

TSQL的TRY...CATCH无法捕获此错误。这个错误属于“编译/重新编译”类型的错误，不能被同一级别执行的CATCH块处理。

来自MSDN：

以下类型的错误，在它们与TRY…CATCH结构在同一执行级别时发生时，不被CATCH块处理： - 阻止批次运行的编译错误，例如语法错误。 - 发生在语句级别重新编译期间的错误，例如由于延迟名称解析而导致编译后发生的对象名称解析错误。

...

您可以使用TRY...CATCH来处理编译期或语句级别重新编译期间发生的错误，通过在TRY块内执行生成错误代码的单独批处理。例如，您可以将代码放在存储过程中或使用sp_executesql执行动态Transact-SQL语句。这允许TRY...CATCH在比错误发生更高的执行级别上捕获错误。例如，下面的代码显示了生成对象名称解析错误的存储过程。包含TRY...CATCH结构的批处理正在比存储过程更高的级别上执行；而在较低级别上发生的错误被捕获。

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

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

如果您希望所有内容都在一个脚本中，可以将其制作为临时存储过程，但您需要在创建存储过程时处理编译错误。这不是很美观，但它能够工作。

-- 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;

- Jon Senchyna

---

基本上，如果一个编译过的存储过程依赖于被重命名的对象，那么CATCH将无法工作。可能有一些解决方法，但基本上需要修复存储过程以考虑重命名的对象。 - Alex Cooper

是的，如果您的表发生变化，您应该更改指向它们的任何存储过程。这并不意味着您的存储过程可以继续工作，它只是捕获和记录错误，这可能有助于识别在应用程序运行期间某个时候被忽略的存储过程，并且直到稍后才失败的情况。我不会说我建议将其用于您所有的存储过程，但对于这种特定情况，它是有效的。 - Jon Senchyna

Answer 2

在我的日志记录 INSERT 语句之前的 CATCH 块中，我使用 THROW，并遇到了与您相同的问题。一旦我将 THROW 移到日志记录 INSERT 语句之后，它就能正常工作了。看起来 THROW 可能会终止会话。

虽然你的代码示例中没有使用 THROW，但我认为这可能有助于其他人。

- John.Poston

Answer 3

它是你的返回值：“无条件地从查询或过程中退出。返回是立即且完全的，可以在任何时候用于退出过程、批处理或语句块。”

- Russell Fox

---

RETURN语句没有被执行，这是由于出现了“列名或提供的值的数量与表定义不匹配”的错误提示。 - Michael Fredrickson

谢谢Russell - 尽管我同意Michael的观点，但我相当确定Return没有被执行到。感谢大家对此的帮助 - 我认为Ivan已经给出了答案。 - DanBennett