SQL Server 错误处理模板

引言

以下是我们可以用来管理从 SQL Server 2005 开始的错误的错误处理模板。在跳转到模板之前，我们首先讨论一些将在后面的技巧中使用的术语。要了解 SQL Server 错误的的基本知识，请访问我的第一篇文章 SQL server 错误详情。

背景

@@TRANCOUNT

它返回当前会话中活动事务的数量。它可以用来查找事务的嵌套级别。
BEGIN TRAN 语句将事务计数递增 1。
COMMIT TRAN 语句将事务计数递减 1。
ROLLBACK TRAN 将事务计数重置为 0。
它不能用来检查当前事务是否可以提交。

SAVE TRANSACTION

它允许一种回滚事务一部分的机制。当出现错误时，它将帮助我们提交一个大型脚本批处理中的事务的一部分。它不会影响 @@TRANCOUNT 值。但在使用 Rollback 时，我们需要指定保存点名称。如果未指定保存点名称，它将回滚所有事务。ROLLBACK TRANSACTION savepoint_name 不会递减 @@TRANCOUNT 值。

XACT_STATE

在 SQL Server 2005 中引入。XACT_STATE() 是一个标量函数。它向我们提供有关请求当前状态的信息。它还说明事务是否可以提交。但它不能用来确定嵌套事务。它返回 3 个值

• 1：当前请求具有活动用户事务。事务可以提交。
• 0：没有活动事务。
  
• -1：当前请求具有活动用户事务，但发生了一些错误并且事务无法提交。

TRY--CATCH

它由 SQL Server 2005 提供，用于像在 C# 等其他编程语言中一样处理错误。但这里我们没有 FINALLY 块。
如果在 TRY 块中引发任何错误，控制权将传递到 CATCH 块。

模板中的错误处理

我们可以使用 try catch 来处理错误，如下所示

BEGIN TRY
 -- Code goes here
END TRY
BEGIN CATCH
  ---Error handling code goes here
END CATCH

以下是可以用来处理错误的完整模板

CREATE PROCEDURE [USP_Procedure_Name]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @TranCountAtStart INT;
    SET @TranCountAtStart = @@TRANCOUNT;

    BEGIN TRY
        IF @TranCountAtStart = 0
            BEGIN TRANSACTION
        ELSE
            SAVE TRANSACTION USP_Procedure_Name;

            -- put code here

        IF @TranCountAtStart = 0
            COMMIT;
    END TRY

    BEGIN CATCH
    
    DECLARE @ERRORMESSAGE    NVARCHAR(512),
            @ERRORSEVERITY    INT,
            @ERRORNUMBER    INT,
            @ERRORSTATE        INT,
            @ERRORPROCEDURE    SYSNAME,
            @ERRORLINE        INT

    SELECT
            @ERRORMESSAGE    = ERROR_MESSAGE(),
            @ERRORSEVERITY    = ERROR_SEVERITY(),
            @ERRORNUMBER    = ERROR_NUMBER(),
            @ERRORSTATE        = ERROR_STATE(),
            @ERRORPROCEDURE    = ERROR_PROCEDURE(),
            @ERRORLINE        = ERROR_LINE()

    SET @ERRORMESSAGE = 
    (
    SELECT  
      'Error:'        +    convert(nvarchar(50),@ErrorNumber) + space(1) +
      'Severity:'        +    convert(nvarchar(50),@ErrorSeverity) + space(1) +
      'State:'        +    convert(nvarchar(50),@ErrorState) + space(1) +
      'Routine_Name: '    +    isnull(@ErrorProcedure,'') + space(1) +
      'Line:'        +    convert(nvarchar(50),@ErrorLine) + space(1) +
      'Message: '        +    @ErrorMessage + space(1) +
      'ExecutedAs:'    +    SYSTEM_USER + space(1) +
      'Database:'        +    DB_NAME() + space(1) +
      'OSTime:'        +    convert(nvarchar(25),CURRENT_TIMESTAMP,121)
    )

            IF @TranCountAtStart = 0
            ROLLBACK TRANSACTION

            IF  @TranCountAtStart > 0
            ROLLBACK TRANSACTION USP_Procedure_Name;
            --We can also save the error details to a table for later reference here.
            RAISERROR (@ERRORMESSAGE,16,1)
    END CATCH
END

如果您使用的是 SQL Server 2012 或更高版本，则可以使用以下模板

CREATE PROCEDURE [USP_Procedure_Name]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @TranCountAtStart INT;
    SET @TranCountAtStart = @@TRANCOUNT;

    BEGIN TRY
        IF @TranCountAtStart = 0
            BEGIN TRANSACTION
        ELSE
            SAVE TRANSACTION USP_Procedure_Name;

            -- put code here

        IF @TranCountAtStart = 0
            COMMIT;
    END TRY

    BEGIN CATCH
    
    DECLARE @ERRORMESSAGE    NVARCHAR(512),
            @ERRORSEVERITY    INT,
            @ERRORNUMBER    INT,
            @ERRORSTATE        INT,
            @ERRORPROCEDURE    SYSNAME,
            @ERRORLINE        INT,
            @XASTATE INT
    SELECT
            @ERRORMESSAGE    = ERROR_MESSAGE(),
            @ERRORSEVERITY    = ERROR_SEVERITY(),
            @ERRORNUMBER    = ERROR_NUMBER(),
            @ERRORSTATE        = ERROR_STATE(),
            @ERRORPROCEDURE    = ERROR_PROCEDURE(),
            @ERRORLINE        = ERROR_LINE()

    SET @ERRORMESSAGE = 
    (
    SELECT  
      'Error:'        +    convert(nvarchar(50),@ErrorNumber) + space(1) +
      'Severity:'        +    convert(nvarchar(50),@ErrorSeverity) + space(1) +
      'State:'        +    convert(nvarchar(50),@ErrorState) + space(1) +
      'Routine_Name: '    +    isnull(@ErrorProcedure,'') + space(1) +
      'Line:'        +    convert(nvarchar(50),@ErrorLine) + space(1) +
      'Message: '        +    @ErrorMessage + space(1) +
      'ExecutedAs:'    +    SYSTEM_USER + space(1) +
      'Database:'        +    DB_NAME() + space(1) +
      'OSTime:'        +    convert(nvarchar(25),CURRENT_TIMESTAMP,121)
    )

        SELECT @XASTATE = XACT_STATE();

        IF @XASTATE = - 1
            ROLLBACK;

        IF @XASTATE = 1
            AND @TranCountAtStart = 0
            ROLLBACK

        IF @XASTATE = 1
            AND @TranCountAtStart > 0
            ROLLBACK TRANSACTION USP_Procedure_Name;
            --We can also save the error details to a table for later reference here.
            RAISERROR (@ERRORMESSAGE,16,1)
    END CATCH
END

这些也可以用来管理嵌套事务。

关注点

您可以检查其正确性并在您的开发中使用它。非常欢迎您的建议和反馈。

历史

• 2014 年 9 月 30 日：初始版本