SQL Server T-SQL 错误处理的最佳实践是什么?

2021-12-26 00:00:00 error-handling sql-server-2005 tsql sql-server

我们有一个主要用 SQL Server 7.0 编写的大型应用程序，其中所有数据库调用都是对存储过程的.我们现在运行的是 SQL Server 2005，它提供了更多的 T-SQL 功能.

We have a large application mainly written in SQL Server 7.0, where all database calls are to stored procedures. We are now running SQL Server 2005, which offers more T-SQL features.

在几乎每个 SELECT、INSERT、UPDATE 和 DELETE 之后，@@ROWCOUNT 和 @@ERROR 都会被捕获到局部变量中并评估问题.如果出现问题，请执行以下操作:

After just about every SELECT, INSERT, UPDATE, and DELETE, the @@ROWCOUNT and @@ERROR get captured into local variables and evaluated for problems. If there is a problem the following is done:

  • 错误信息输出参数设置
  • 回滚(如有必要)已完成
  • 信息被写入(插入)到日志表
  • 返回一个错误编号，此过程唯一(如果致命，则为正，警告为负)

它们都不会检查行(仅在已知时)，有些会因或多或少的日志/调试信息而有所不同.此外，行逻辑有时与错误逻辑分离(在 WHERE 子句中检查并发字段的更新中，rows=0 表示其他人已更新数据).然而，这是一个相当通用的例子:

They all don't check the rows (only when it is known) and some differ with more or less log/debug info. Also, the rows logic is somethimes split from the error logic (on updates where a concurrency field is checked in the WHERE clause, rows=0 means someone else has updated the data). However, here is a fairly generic example: 

SELECT, INSERT, UPDATE, or DELETE

SELECT @Error=@@ERROR, @Rows=@@ROWCOUNT
IF @Rows!=1 OR @Error!=0
BEGIN
    SET @ErrorMsg='ERROR 20, ' + ISNULL(OBJECT_NAME(@@PROCID), 'unknown') 
                               + ' - unable to ???????? the ????.'
    IF @@TRANCOUNT >0
    BEGIN 
        ROLLBACK
    END

    SET @LogInfo=ISNULL(@LogInfo,'')+'; '+ISNULL(@ErrorMsg,'')+
        + ' @YYYYY='        +dbo.FormatString(@YYYYY)
        +', @XXXXX='        +dbo.FormatString(@XXXXX)
        +', Error='         +dbo.FormatString(@Error)
        +', Rows='          +dbo.FormatString(@Rows)

    INSERT INTO MyLogTable (...,Message) VALUES (....,@LogInfo)

    RETURN 20

END

我正在考虑用 TRY-CATCH T-SQL 取代我们的做法.我已经阅读了关于 TRY...CATCH (Transact-SQL) 语法，所以不要只是发布一些总结.我正在寻找任何好的想法以及如何最好去做或改进我们的错误处理方法.它不必是 Try-Catch，只需使用 T-SQL 错误处理的任何好的或最佳实践即可.

I am looking into replacing how we do this with the TRY-CATCH T-SQL. I've read about the TRY...CATCH (Transact-SQL) syntax, so don't just post some summary of that. I'm looking for any good ideas and how best to do or improve our error handling methods. It doesn't have to be Try-Catch, just any good or best practice use of T-SQL error handling.

推荐答案

您应该阅读以下内容:

http://www.sommarskog.se/error-handling-I.html

我不能高度推荐该链接.有点长，但很好.

I can't recommend that link highly enough. It's a bit long, but in a good way.

前面有一个免责声明，它最初是为 SQL Server 2000 编写的，但它也涵盖了 SQL Server 2005+ 中新的 try/catch 错误处理能力.

There's a disclaimer at the front that it was originally written for SQL Server 2000, but it covers the new try/catch error handling abilities in SQL Server 2005+ as well.

相关文章