SQL：尝试访问无法找到的表时，Try / Catch不会捕获错误 [英] SQL: Try/Catch doesn't catch an error when attempting to access a table that it can't find

问题描述

我创建了一个存储过程，该过程运行许多命令来修改数据。如果一切成功，我只想提交事务。我是通过以下方式使用try-catch块来完成此操作的（其中真实的CATCH块使用RAISERROR返回错误消息）：

I've created a stored procedure that runs a number of commands to modify data. I only want to commit the transaction if everything succeeds. I'm doing this by using a try-catch block in the manner below (where my CATCH block in the real thing uses RAISERROR to return error messages):

BEGIN TRY
  BEGIN TRANSACTION
  UPDATE Table1 SET MyVarcharColumn = 'test'
  UPDATE Table2 SET MyBitColumn = 1
  UPDATE Table3 SET MyIntColumn = 42
  COMMIT TRANSACTION
END TRY
CATCH
  ROLLBACK TRANSACTION
END CATCH

这就是我想要的方式。例如，如果我将MyBitColumn设置为'b'而不是1，则捕获了错误，控制权流到了CATCH，并且未提交事务。

That works the way I want it to. If, for example, I set MyBitColumn to 'b' instead of 1, the error is caught, control flows to the CATCH, and the transaction is not commited.

一个我注意到的问题是，如果说Table3在数据库中不存在，则会出错（无效的对象名），但是CATCH块将永远不会执行，并且事务保持打开状态。

One issue I've noticed is that if, say, Table3 does not exist in the database then it errors out (invalid object name), but the CATCH block is never executed and the transaction remains open.

我想处理这个问题，以解决数据库被修改的任何（远程）可能性（或在适当添加此存储过程但某些表未添加的情况下发生的事情）。

I want to handle this to take care of any (remote) possibility that a database gets modified (or something happens where this stored procedure is added properly, but one of the tables isn't).

我应该如何处理这些错误情况？

How should I handle these error cases?

-谢谢您的帮助。

推荐答案

在脚本的开头使用 SET XACT_ABORT

SET XACT_ABORT ON

当SET XACT_ABORT为ON时，如果Transact-SQL语句引发
运行时错误，整个事务将终止并回滚。

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

我认为这不可能：

当
与TRY处于同一执行级别时，以下类型的错误不会由CATCH块处理…CATCH构造：

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.

参考。

以下示例显示了对象名称的命名方式SELECT语句生成的解析错误
未被TRY…CATCH
构造捕获，但是我在存储过程中执行相同的SELECT
语句时被CATCH块捕获。

The following example shows how an object name resolution error generated by a SELECT statement is not caught by the TRY…CATCH construct, but is caught by the CATCH block when the same SELECT statement is executed inside a stored procedure.

USE AdventureWorks2012;
GO

BEGIN TRY
    -- Table does not exist; object name resolution
    -- error not caught.
    SELECT * FROM NonexistentTable;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

未捕获到错误并且控制通过从TRY…CATCH
构造中移到更高的水平。

The error is not caught and control passes out of the TRY…CATCH construct to the next higher level.

这篇关于SQL：尝试访问无法找到的表时，Try / Catch不会捕获错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！