异常处理不处理SQL-SERVER中的错误

We are handling Exceptions in our stored procedures using Try/Catch block. In a stored procedure I have intentionally made an error to check how the exceptions being handled. I wrote a stored procedure like as below,

我们正在使用Try / Catch块处理存储过程中的异常。在存储过程中,我故意做出错误来检查处理异常的方式。我写了一个如下的存储过程,

CREATE PROCEDURE TEST
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
    BEGIN TRAN

              ;WITH CTE
              AS
              (SELECT TOP 10 * 
               FROM student)

               SELECT * FROM CTE

               SELECT * INTO #VAL
               FROM CTE


      IF @@ERROR = 0
              BEGIN
                  COMMIT TRAN;
              END
      END TRY
      BEGIN CATCH
              SELECT @@ERROR AS ERROR, ERROR_LINE() AS [Error Line], ERROR_MESSAGE() AS [Error Message] 
      ROLLBACK TRAN;
      END CATCH
SET NOCOUNT OFF;
END

In the Above Stored procedure I have used a CTE multiple Times. I expected that SQL-SERVER will handle the exception but it didn't. While Executing the Above stored Procedure I got below error

在上面存储过程中,我多次使用CTE。我希望SQL-SERVER能处理异常,但事实并非如此。在执行以上存储过程时,我得到以下错误

Msg 208, Level 16, State 1, Procedure TEST, Line 16

Msg 208,Level 16,State 1,Procedure TEST,Line 16

Invalid object name 'CTE'.

无效的对象名称'CTE'。

Msg 266, Level 16, State 2, Procedure TEST, Line 16

消息266,16级,状态2,程序测试,第16行

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2.

EXECUTE之后的事务计数表示BEGIN和COMMIT语句的数量不匹配。先前的计数= 1,当前计数= 2。

Why the Exception is not handled ? Could someone give idea on this?

为什么不处理异常?有人可以就此提出想法吗?

Thanks for the help.

谢谢您的帮助。

1 个解决方案

#1

stored procedures follow deferred object name resolution while executing.So in this case cte is a non existent object to it .

存储过程在执行时遵循延迟对象名称解析。因此在这种情况下,cte是一个不存在的对象。

Further try catch cannot handle this type of errors

进一步尝试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:

当CATCH块出现在与TRY ... CATCH构造相同的执行级别时,它们不会处理以下类型的错误:

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

Please see MSDN for more info(see errors unaffected by try catch )

有关详细信息,请参阅MSDN(请参阅不受try catch影响的错误)

#1