主页 / dba / 问题 / 2296
Accepted
kacalapy
Asked: 2011-04-21 11:19:55 +0800 CST

在 SQL 2005 存储过程中添加错误处理的最佳方法是什么？

  • 772

什么是使存储过程足够健壮以使其可以很好地扩展并且还包含错误处理的好方法？

此外，在存储过程中处理多个错误场景并拥有一个智能反馈系统，将有意义的错误信息返回给调用应用程序的最佳方法是什么？

sql-server-2005 stored-procedures

3 个回答

  1. Best Answer

    Alex Kuznetsov 在他的《防御性数据库编程》（第 8 章）一书中有一个很棒的章节，涵盖了 T-SQL TRY...CATCH、T-SQL 事务和 SET XACT_ABORT 设置，以及使用客户端错误处理。它将帮助您确定哪些选项对您需要完成的任务最有意义。

    可在此站点免费获得。我与这家公司没有任何关系，但我确实拥有那本书的硬拷贝版本。

    亚历克斯很好地解释了这个主题的很多小细节。

    根据尼克的要求......（但并非所有这些都在本章中）

    在扩展方面，您需要非常诚实地了解哪些活动需要在数据库代码中，哪些应该在应用程序中。有没有注意到快速执行的代码往往会回归到为每个方法设计一个关注点？

    最简单的沟通方式是自定义错误代码 (> 50,000)。它也非常快。这确实意味着您必须使数据库代码和应用程​​序代码保持同步。使用自定义错误代码，您还可以在错误消息字符串中返回有用信息。因为您有一个严格针对这种情况的错误代码，所以您可以在应用程序代码中编写一个针对错误数据格式定制的解析器。

    另外，哪些错误条件需要数据库中的重试逻辑？如果您想在 X 秒后重试，那么您最好在应用程序代码中处理它，这样事务就不会阻塞太多。如果您只是立即重新提交 DML 操作，则在 SP 中重复它可能更有效。但请记住，您可能必须复制代码或添加一层 SP 才能完成重试。

    真的，这是目前 SQL Server 中 TRY...CATCH 逻辑的最大痛点。可以做到，但有点笨拙。寻找 SQL Server 2012 中的一些改进，特别是重新抛出系统异常（保留原始错误号）。此外，还有FORMATMESSAGE，它在构建错误消息时增加了一些灵活性，特别是用于记录目的。

    • 12

  2. 这是我们的模板（删除了错误日志）

    笔记：

    • 如果没有 XACT_ABORT，所有 TXN 开始和提交/回滚必须成对出现
    • 提交递减@@TRANCOUNT
    • 回滚将@@TRANCOUNT 返回为零，因此您会收到错误 266
    • 你不能只回滚当前层（例如在回滚时减少@@TRANCOUNT）
    • XACT_ABORT 抑制错误 266
    • 每个存储过程必须符合相同的模板，因此每个调用都是原子的
    • 由于 XACT_ABORT，回滚检查实际上是多余的。然而，它让我感觉更好，没有它看起来很奇怪，并且允许你不想要它的情况
    • 这允许客户端 TXN（如 LINQ）
    • Remus Rusanu有一个类似的 shell，它使用保存点。我更喜欢原子数据库调用，不要像他们的文章那样使用部分更新

    ...所以不要创建比您需要的更多的 TXN

    然而，

    CREATE PROCEDURE [Name]
AS
SET XACT_ABORT, NOCOUNT ON

DECLARE @starttrancount int

BEGIN TRY
    SELECT @starttrancount = @@TRANCOUNT

    IF @starttrancount = 0
        BEGIN TRANSACTION

       [...Perform work, call nested procedures...]

    IF @starttrancount = 0 
        COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION
    RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
END CATCH
GO
    • 7

  3. 我使用 Try/Catch，但我也会收集尽可能多的信息，并在回滚后将其写入错误日志。在此示例中，“LogEvent”是一个写入 EventLog 表的存储过程，其中包含所发生事件的详细信息。GetErrorInfo() 是一个返回确切错误消息的函数调用。

    当发生错误时，收集信息，过程跳到错误处理部分并发出回滚。信息被写入日志，然后程序退出。

    考虑到涉及的额外过程/函数调用，它似乎有点过头了。但是，此方法在尝试调试问题时非常有用。

    exec LogEvent @Process, @Database, '试图插入等等等等'
开始尝试
  插入 MyTable
  选择值
    来自 MyOtherTable

  选择@rowcount = @@ROWCOUNT
结束尝试
-- 错误处理
开始捕捉
  选择@error = ERROR_NUMBER(),
         @rowcount = -1，
         @TableAction = '插入',
         @TableName = @Database + '.MyTable',
         @AdditionalInfo = '（尝试插入等等等等）' + dbo.GetErrorInfo()
   GOTO 表访问错误
结束捕获

.
.
.
.

表访问错误：
IF (@@TRANCOUNT > 0) 回滚
选择@output = 上（@TableAction）+
       ' ERROR - 发生错误时 ' +
       案例（@TableAction）
         当'更新'然后'更新'
         当'删除'然后'删除'
         否则@TableAction + 'ing'
       结束 +
       '记录' +
       案例（@TableAction）
         当'选择'然后'来自'
         当“更新”然后“进入”
         当'插入'然后'进入'
         否则“来自”   
         结束 +
         ' ' + @TableName + ' 表。'
选择 @output = @output + ' @@ERROR: ' + convert(varchar(8),@error)
选择@output = @output + '@@ROWCOUNT:' + 转换（varchar（8），@rowcount）

选择 @output = @output + isnull(@AdditionalInfo,'')
执行 LogEvent @Process、@Database、@Output
RAISERROR(@output,16,1) 带日志
选择@ReturnCode = -1
转到 THE_EXIT
    • 3

相关问题