SQL Server T-SQL错误处理的最佳做法是什么？ [英] What is the best practice use of SQL Server T-SQL error handling?
问题描述
我们有一个主要用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:
- 错误消息输出参数设置
- 回滚（如果需要）完成
- 信息被写入（INSERT）到日志表
- 返回一个错误号，这个过程是唯一的（如果致命的，负面的是警告）
- error message output parameter is set
- rollback (if necessary) is done
- info is written (INSERT) to log table
- return with a error number, unique to this procedure (positive if fatal, negative is warning)
他们都不检查行（只有当它是已知的）较少的日志/调试信息。此外，行逻辑有时会从错误逻辑中分离（在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编写的，但它涵盖了新的try / catch SQL Server 2005+中的错误处理能力。
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.
这篇关于SQL Server T-SQL错误处理的最佳做法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
