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

Question

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

在几乎每个SELECT、INSERT、UPDATE和DELETE之后，都会捕获@@ROWCOUNT和@@ERROR到本地变量并进行问题评估。如果存在问题，则执行以下操作：

• 设置错误消息输出参数
• 回滚（如果必要）
• 将信息写入日志表（INSERT）
• 返回一个错误号，该号码对于此存储过程是唯一的（正数表示致命错误，负数表示警告）

他们都不检查行数（仅在已知情况下检查），有些差异的记录/调试信息也是如此。而且，行逻辑有时会与错误逻辑分开（在更新中，WHERE子句中检查并发字段，行= 0表示其他人已更新数据）。但是，这里是一个相当通用的示例：

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错误处理的任何好的或最佳实践。

- KM.

Answer 1

你应该阅读这篇文章：

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

我极力推荐这个链接。虽然有点长，但它是一种好的方式。

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

- Joel Coehoorn

---

我觉得它跳过了SQL Server 2005的内容，但总体来说还是很棒的。他网站上的其他内容也很不错。 - gbn

从 SQL Server 2005 开始，请在此处开始阅读：http://www.sommarskog.se/error_handling/Part1.html。 - Mark Schultheiss

Answer 2

目前我们在执行任何查询时使用此模板（如果您不需要在例如DDL语句中使用事务，则可以省略Transaction部分）：

BEGIN TRANSACTION
BEGIN TRY
    // do your SQL statements here

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage

    ROLLBACK TRANSACTION
END CATCH

当然，你可以轻松将捕获的异常插入到你的错误日志表中。

这对我们来说非常有效。你甚至可以使用代码生成（例如CodeSmith）或一些自定义C#代码自动化地将一些旧存储过程转换为新格式。

- marc_s

---

为什么要在 TRY 块之外开始事务？是否有优势？MSDN 上的所有示例都显示 BEGIN TRAN 是 TRY 块内的第一条语句。http://technet.microsoft.com/en-us/library/ms179296%28v=sql.105%29.aspx为什么要在 TRY 块之外开始事务？是否有优势？MSDN 上的所有示例都显示 BEGIN TRAN 是 TRY 块内的第一条语句。http://technet.microsoft.com/en-us/library/ms179296%28v=sql.105%29.aspx - Davos

如果使用事务，还应考虑XACT_STATE：https://msdn.microsoft.com/zh-cn/library/ms189797.aspx - Alan Larimer

当出现错误并且控制权传递到CATCH块时，执行完CATCH块后，控制权是否会传递回导致错误的语句后面的语句，从而继续执行COMMIT TRANSACTION，还是控制权传递到CATCH块后面的语句？ - vinkomlacic

@user8810865：不会 - 执行会在 CATCH 块之后继续。 - marc_s

Answer 3

没有一套固定的最佳错误处理实践。关键在于您的需求以及保持一致性。

以下是一个存储电话号码的表和存储过程示例：

 SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Phone](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [Phone_Type_ID] [int] NOT NULL,
        [Area_Code] [char](3) NOT NULL,
        [Exchange] [char](3) NOT NULL,
        [Number] [char](4) NOT NULL,
        [Extension] [varchar](6) NULL,
     CONSTRAINT [PK_Phone] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF
    GO
    /**/

    CREATE PROCEDURE [dbo].[usp_Phone_INS]
         @Customer_ID INT
        ,@Phone_Type_ID INT
        ,@Area_Code CHAR(3)
        ,@Exchange CHAR(3)
        ,@Number CHAR(4)
        ,@Extension VARCHAR(6)
    AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @Err INT, @Phone_ID INT

        BEGIN TRY
            INSERT INTO Phone
                (Phone_Type_ID, Area_Code, Exchange, Number, Extension)
            VALUES
                (@Phone_Type_ID, @Area_Code, @Exchange, @Number, @Extension)
            SET @Err = @@ERROR
            SET @Phone_ID = SCOPE_IDENTITY()
            /* 
                Custom error handling expected by the application.
                If Err = 0 then its good or no error, if its -1 or something else then something bad happened.
            */
            SELECT ISNULL(@Err,-1) AS Err, @Phone_ID
        END TRY
        BEGIN CATCH
            IF (XACT_STATE() <> 0)
                BEGIN
                    ROLLBACK TRANSACTION
                END

            /* 
                Add your own custom error handling here to return the passed in paramters. 
                I have removed my custom error halding code that deals with returning the passed in parameter values.
            */

            SELECT ERROR_NUMBER() AS Err, ISNULL(@Phone_ID,-1) AS ID
        END CATCH
    END

- DBAndrew

Answer 4

看起来你已经非常熟练了。我怀疑你比其他95%的SQL程序员都要做得更多。

你应该在这里找到一些有趣的信息：

• 检测和报告存储过程中的错误-第1部分：SQL Server 2000
• 检测和报告存储过程中的错误-第2部分：SQL Server 2005 TRY-CATCH块*

一个[无关的]建议：开始使用'<>'代替'!='。

[* SQL Junkies已经消失，所以第二篇文章不可用。我会尝试在其他地方重新发布并更新链接。]

- Rob Garrison

---

为什么要使用“<>”而不是“！=”？ - KM.

ANSI规范指定了< >。许多数据库也支持！=，但这不是标准。 - Joel Coehoorn

这只是一个建议而已。唉。 - Rob Garrison

但既然你知道了，为什么不把它做对呢？ - Joel Coehoorn

您可能需要将您的SQL 2000代码转移到SQL 2005或SQL 2008。微软在Books online中写道，许多功能将被弃用并最终删除。其中许多功能是非ANSI扩展，将被淘汰。尽管如此，我认为您不应该编写期望移植到例如DB2的代码，因为这是不可能的。 - MatthewMartin