T-SQL TRY CATCH 的问题? [英] Issues with T-SQL TRY CATCH?

问题描述

我们目前正在使用 SQL 2005，我正在将旧的 Foxpro 系统迁移到由 SQL Server 支持的新 Web 应用程序.我在 T-SQL 中使用 TRY CATCH 进行事务处理，它似乎工作得很好.一位在职的其他程序员对此感到担忧，因为他说他听说过一些问题，其中标语并不总是能捕捉到错误.我已经把 sproc 打败了，不能让它失败(错过一个捕获)，我在网上搜索发现的唯一问题是它不会返回正确的错误号错误号 <5000. 有没有人在 T-SQL 中遇到过 TRY CATCH 的任何其他问题 - 特别是如果它错过了一个捕获?感谢您提供的任何意见.

We are currently on SQL 2005 at work and I am migrating an old Foxpro system to new web application backed by SQL Server. I am using TRY CATCH in T-SQL for transaction processing and it seems to be working very well. One of the other programmers at work was worried about this as he said he had heard of issues where the catch phrase did not always catch the error. I have beat the sproc to death and cannot get it to fail (miss a catch) and the only issues I have found searching around the net is that it will not return the correct error number for error numbers < 5000. Has anyone experienced any other issues with TRY CATCH in T-SQL - especially if it misses a catch? Thanks any input you may wish to provide.

推荐答案

TRY ... CATCH 不能捕获所有可能的错误，但未捕获的错误在 BOL 不受 TRY…CATCH 结构影响的错误

TRY ... CATCH doesn't catch every possible error but the ones not caught are well documented in BOL Errors Unaffected by a TRY…CATCH Construct

TRY…CATCH 结构不会捕获以下条件:

TRY…CATCH constructs do not trap the following conditions:

• 严重性为 10 或更低的警告或信息性消息.
• 严重性为 20 或更高的错误会停止 SQL Server数据库引擎任务处理会议.如果发生错误严重程度为 20 或更高，并且数据库连接没有中断，TRY…CATCH 将处理错误.
• 注意，例如客户端中断请求或中断客户端连接.
• 当系统管理员使用 KILL 结束会话时声明.

以下类型的错误不是由 CATCH 块处理发生在相同的执行级别作为 TRY…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:

• 编译错误，例如语法错误，会阻止批处理运行.
• 在语句级重新编译期间发生的错误，例如对象名称解析错误编译后发生，因为延迟名称解析.

这些错误返回到级别运行批处理，存储过程，或触发.

These errors are returned to the level that ran the batch, stored procedure, or trigger.

这篇关于T-SQL TRY CATCH 的问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！