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

本文介绍了SQL Server T-SQL 错误处理的最佳实践是什么?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我们有一个主要用 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.

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

问题描述

推荐答案

相关文档推荐