SQL2005错误处理

  SQL2005错误处理 <script></script>

由 SQL Server 数据库引擎 引起的很多错误都能以编程方式来捕获和解决。Transact-SQL 语言和应用程序用于访问存储在数据库引擎 中的数据的数据访问应用程序编程接口 (API) 都提供了错误处理能力。

每个数据库引擎 错误都包含以下属性：错误号、消息字符串、严重性、状态、过程名称和行号.

来自数据库引擎 的错误可以在两种级别上处理：

通过向 Transact-SQL 批处理、存储过程、触发器或用户定义函数添加错误处理代码，可以在数据库引擎 中处理错误。Transact-SQL 错误处理机制包括 TRY...CATCH 构造、RAISERROR 语句和 @@ERROR 函数。

错误可以返回到调用应用程序并在应用程序代码中进行处理。应用程序用于访问数据库引擎 的每个 API 都提供了将错误信息传送回应用程序的机制。

在 TRY...CATCH 构造的 CATCH 块的作用域内，您可以使用以下系统函数：

ERROR_LINE()，返回出现错误的行号。

ERROR_MESSAGE()，返回将返回给应用程序的消息文本。该文本包括为所有可替换参数提供的值，如长度、对象名或时间。

ERROR_NUMBER() 返回错误号。

ERROR_PROCEDURE()，返回出现错误的存储过程或触发器的名称。如果在存储过程或触发器中未出现错误，该函数返回 NULL。

ERROR_SEVERITY() 返回严重性。

ERROR_STATE()，返回状态。

注意：如果要在运行语句之后同时引用 @@ERROR 和 @@ROWCOUNT，则必须在同一语句中引用它们。

例如：
       UPDATE TABLE SET COLUMN1=XXX,.......
       PRINT @@ROWCOUNT
       PRINT @@ERROR

这里捕获到的错误号永远是0，因为他捕获到的是PRINT这句的错误号，明显PRINT @@ROWCOUNT这句永远是
正确的，这个问题存在于很多应用程序开发者当中。
以下是正确的达到预期的效果：

      DECLARE @ERROR INT
      DECLARE @ROWCOUNT INT
      UPDATE TABLE SET COLUMN1=XXX,........
      SELECT @ERROR=@@ERROR,@ROWCOUNT=@@ROWCOUNT
      PRINT @ROWCOUNT
      PRINT @ERROR

Transact-SQL 代码中的错误可使用 TRY…CATCH 构造处理，此功能类似于 Microsoft Visual C++ 和 Microsoft Visual C# 语言的异常处理功能。TRY…CATCH 构造包括两部分：一个 TRY 块和一个 CATCH 块。如果在 TRY 块中所包含的 Transact-SQL 语句中检测到错误条件，控制将被传递到 CATCH 块（可在此块中处理该错误）。

使用 TRY...CATCH 构造时，请遵循下列规则和建议：

1.每个 TRY...CATCH 构造都必须包含在一个批处理、存储过程或触发器中。例如，不能将 TRY 块放置在一个批处理中而将关联的 CATCH 块放置在另一个批处理中。

2.CATCH 块必须紧跟 TRY 块。

3.TRY...CATCH 构造可以进行嵌套，也就是说在其他 TRY 和 CATCH 块内放置 TRY...CATCH 构造。当嵌套的 TRY 块中出现错误时，程序控制将传递到与嵌套的 TRY 块关联的 CATCH 块.

4.若要处理给定的 CATCH 块中出现的错误，请在指定的 CATCH 块中编写 TRY...CATCH 块。

5.TRY...CATCH 块不处理导致数据库引擎 终止连接的严重性为 20 或更高的错误。但是，只要连接不终止，TRY...CATCH 就会处理严重性为 20 或更高的错误。

6.严重性为 10 或更低的错误被视为警告或信息性消息，TRY...CATCH 块不处理此类错误.

7.即使批处理位于 TRY...CATCH 构造的作用域内，关注消息仍将终止该批处理。分布式事务失败时，Microsoft 分布式事务处理协调器 (MS DTC) 将发送关注消息。MS DTC 用于管理分布式事务。

对于与 TRY...CATCH 构造在同一执行级别上发生的错误，TRY...CATCH 将不处理以下两类错误：

编译错误，例如阻止批处理执行的语法错误。

语句级重新编译过程中出现的错误，例如由于名称解析延迟而造成在编译后出现对象名称解析错误。

如：

    BEGIN TRY
        PRINT N'Starting execution;
        SELECT * FROM NonExistentTable;'
    END TRY
    BEGIN CATCH
        SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_MESSAGE() AS ErrorMessage;
    END CATCH;
    GO
如果“NonExistentTable;”不存在，那么编译就会出错，但这时候，TRY快无法捕获这个错误，但是
如果在存储过程中放置代码或使用 sp_executesql 执行动态 Transact-SQL 语句，则可以解决问题

修改代码如下：

    DECLARE @SQL NVARCHAR(1000)
    BEGIN TRY
        PRINT N'Starting execution';
        SET @SQL=N'SELECT * FROM NonExistentTable;';
        EXEC SP_EXECUTESQL @SQL
    END TRY
    BEGIN CATCH
        SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_MESSAGE() AS ErrorMessage;
    END CATCH;
    GO

则结果就是我们想要的。

有关详细信息，请参阅BOOS ONLINE的"延迟名称解析和编译".

在 TRY...CATCH 构造中，事务可以进入一种状态：事务保持打开但无法提交。事务无法执行写事务日志的任何操作，例如修改数据或尝试回滚到保存点。但是，在此状态下，事务获取的锁将被维护，并且连接也保持打开。发出 ROLLBACK 语句之前，事务的运行不可逆。

发生错误时，事务在 TRY 块内进入无法提交状态，否则此错误将终止该事务。例如，数据定义语言 (DDL) 语句（如 CREATE TABLE）中的大多数错误或 SET XACT_ABORT 设置为 ON 时出现的大多数错误都在 TRY 块外终止事务，而在 TRY 块内显示为事务无法提交。

CATCH 块中的代码可以通过使用 XACT_STATE 函数来测试事务的状态。如果会话中包含无法提交的事务，XACT_STATE 将返回 -1。如果 XACT_STATE 返回 -1，CATCH 块将不能执行写日志的任何操作。下面的代码示例生成 DDL 语句错误，并使用 XACT_STATE 测试事务的状态，以便执行最合适的操作。

例如运行以下2段语句就知道区别：

   CREATE TABLE T1
   (
     ID INT
   )
   GO

脚本1：
    BEGIN TRY
        BEGIN TRAN
            INSERT INTO T1 SELECT 'ADF'
        COMMIT TRAN
    END TRY
    BEGIN CATCH
         EXEC UP_GetErrorInfo
    END CATCH
    GO
脚本2：
    BEGIN TRY
        BEGIN TRAN
            INSERT INTO T1 SELECT 'ADF'
        COMMIT TRAN
    END TRY
    BEGIN CATCH
    EXEC UP_GetErrorInfo
        IF XACT_STATE() <> 0
        BEGIN
            ROLLBACK TRANSACTION;
        END
    END CATCH
    GO

说明：UP_GetErrorInfo代码如下：

CREATE PROCEDURE [dbo].[UP_GetErrorInfo]
AS
    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;