在SQL Server2005中进行错误捕捉。

 

任何程序都可能出现错误，在 SQL Server 中执行 Transact-SQL 也不例外。如果在 Transact-SQL 中发生了错误，一般有两种捕捉错误的方法，一种是在客户端代码（如 C# 、 Delphi 等 ) 中使用类似 try...catch 的语句进行捕捉；另外一种就是在 Transact-SQL 中利用 Transact-SQL 本身提供的错误捕捉机制进行捕捉。如果是因为 Transact-SQL 语句的执行而产生的错误，如键值冲突，使用第一种和第二种方法都可以捕捉，但是如果是逻辑错误，使用客户端代码进行捕捉就不太方便。因此，本文就如何使用 Transact-SQL 进行错误捕捉进行了讨论。

一、非致命错误 (non-fatal error) 的捕捉

通过执行 Transact-SQL 而产生的错误可分为两种：致命错误 (fatal error) 和非致命错误 (non-fatal error) 。在 Transact-SQL 中只可以捕捉非致命错误 ( 如键值冲突 ) ，而无法捕捉致命错误 ( 如语法错误 ) 。在 Transact-SQL 中可以通过系统变量 @@ERROR 判断最近执行的一条语句是否成功执行。如果发生了错误， @@Error 的值大于 0 ，否则值为 0 。下面举一个例子说明 @@ERROR 的使用。

假设有一个表 table1 ，在这个表中有两个字段 f1 ， f2 。其中 f1 是主键。

INSERT INTO table1 VALUES(1, 'aa') INSERT INTO table1 VALUES(1, 'bb')  -- 这条语句将产生一个错误 IF @@ERROR > 0 PRINT ' 键值冲突 '

当执行第二条语句时发生键值冲突错误， @@ERROR 被赋为错误号 2627 ，因此输出结果显示 ' 键值冲突 ' 。使用 @@ERROR 系统变量时需要注意， @@ERROR 只记录最近一次执行的 Transact-SQL 语句所发生的错误，如果最近一次执行的 Transact-SQL 没有发生错误， @@ERROR 的值为 0 。因此，只能在被捕捉的那条 Transact-SQL 语句后使用 @@ERROR 。

在 SQL Server 中，不仅可以捕捉系统提供的错误，还可以自定义错误。有两种方法可以定义错误信息。

1 、使用 sp_addmessage 系统存储过程添加错误信息，然后使用 RAISERROR 抛出错误。

sp_addmessage 将错误号，错误级别、错误描述等信息添加到系统表中，然后使用 RAISERROR 根据相应的错误号抛出错误信息。用户自定义的信息应该从 50001 开始。

EXEC sp_addmessage @msgnum = 50001, @severity = 16, @msgtext = 'sql encounter an error(%s).', @lang = 'us_english'

EXEC sp_addmessage @msgnum = 50001, @severity = 16, @msgtext = 'sql 遇到了一个错误 (%1!).'

如果使用的 SQL Server 版本是非英语版本，在添加本地错误信息时必须首先添加英文的错误信息。错误描述可以象 c 语言中的 printf 的格式字符串一样使用参数，如 %s 、 %d 。但要注意的是在英文版的错误信息中要使用 %s 、 %d 等形式，而在本地化的错误信息中要使用 %1! 、 %2! 等形式，在每个 %?(1 <= ? <= n) 后需要加一个 ! ，而且 %? 的数目必须和英文版的错误信息的参数一致。

在未插入本地化错误信息时， RAISERROR 将使用英文版的错误信息。当插入本地化错误信息时， RAISERROR 使用本地化的错误信息。

RAISERROR(50001, 16, 1, ' 测试 ') 输出的结果： 服务器 : 消息 50001 ，级别 16 ，状态 1 ，行 1 sql 遇到了一个错误 ( 测试 ).

其中 ' 测试 ' 字符串通过 %1 传入本地化的错误描述字符串中。 2 、直接使用 RAISERROR 将错误抛出。

使用第一种方法虽然使 Transact-SQL 语句看上去更整洁（这种方法类似于在编程语言中使用常量定义错误信息，然后在不同的地方通过错误编号引用这些错误信息。 ) ，但是这样做却使错误信息和数据库的耦合度增加，因为如果将这些带有 RAISERROR 的 Transact-SQL 放到别的 SQL Server 数据库上执行，由于在其它的数据库中还未添加错误信息，因此会产生 RAISERROR 调用错误，除非使用 sp_addmessage 将所需的错误信息再加入到其它的数据库中。

基于上述原因， RAISERROR 不仅可以根据错误代码抛出错误信息，也可以直接通过错误描述格式字符串抛出错误信息。

RAISERROR('sql 遇到了一个错误 (%s)', 16, 1, ' 测试 ')

二、逻辑错误的捕捉

在实际应用中，更多的是由于某些业务要求而产生的逻辑错误。这些错误无法通过 @@ERROR 进行捕捉。如果使用客户端代码进行捕捉，那么 Transact-SQL 必须一条一条地执行。如果使用存储过程，那么发生在存储过程内部的逻辑错误就很难在客户端代码中进行捕捉，因此，下面将讨论如何使用 Transact-SQL 捕捉逻辑错误。

所谓逻辑错误，就是在执行完 Transact-SQL 后，执行结果与业务要求的结果不符而产生的。为了说明如何处理逻辑错误，我们再建立一个表 table2 ，这个表的结构和 table1 完全一样，只是 f1 字段不再是主键了。然后建立一个存储过程，它的功能是在 table1 和 table2 中同时插入一条记录，但是这条记录必须满足两个条件。

1 、 f1 值不能大于 100 。

2 、要插入的记录在 table1 中不存在，如果存在，在 table1 和 table2 中都不插入这条记录。

CREATE PROCEDURE p1(@Num int) AS DECLARE @Error int, @RowCount int BEGIN TRANSACTION INSERT INTO table2 VALUES(@Num, 'p') IF @Num > 100 BEGIN RAISERROR('%s 的值不能大于100。', 16, 1, '@Num')   ROLLBACK TRANSACTION RETURN 1 END ELSE BEGIN SELECT f1 FROM table1 WHERE f1 = @Num IF @@ROWCOUNT > 0 BEGIN RAISERROR('table1中已经存在%d了。', 16, 1, @Num) ROLLBACK TRANSACTION RETURN 2 END ELSE BEGIN INSERT INTO table1 VALUES(@Num, 'p') COMMIT TRANSACTION RETURN 0 END END

在这个存储过程中一开始使用 BEGIN TRANSACTION 显示地开始一个事务，然后当上述两种错误发生时使用 ROLLBACK TRANSACTION 恢复到初始状态，如果成功插入，使用 COMMIT TRANSACTION 提交改变。可以通过如下语句进行调用。

DECLARE @ErrNum int EXEC @ErrNUm = p1 2 PRINT @ErrNum

可以通过 @ErrNum 得到 p1 返回的错误代码，如果返回 0 ，表示执行成功。

sql Server2005 中错误捕捉的新功能

虽然在以前的 SQL Server 版本中可以通过一些技巧实现错误捕捉，但有时需要增加一些额外的开销，如在 p1 中使用了 SELECT 语句。庆幸的是在 SQL Server2005 中提供了和大多数编程语言类似的 try...catch 错误捕捉功能，从而使 Transact-SQL 第一次可以真正地进行错误捕捉。使用 try...catch 可以将 p1 的下半部分改写为如下形式。

ELSE BEGIN BEGIN TRY INSERT INTO table1 VALUES(@Num, 'p')   COMMIT TRANSACTION RETURN 0 END TRY BEGIN CATCH RAISERROR('table1 中已经存在%d了。', 16, 1, @Num) ROLLBACK TRANSACTION RETURN 2 END CATCH END

可以看出，这个改写的部分未使用 SELECT 查询 table1 中是否已经有了某条记录，而是通过数据库的约束来进行判断的。如果键值冲突，就产生了错误，这样 SQL 语句就直接跳到 BEGIN CATCH 中执行错误处理代码。这样做效率要比上一个版本高得多，而且如果将 RAISERROR 去掉， p1 就不会抛出任何错误，只是返回了一个错误码，这样有利于客户端代码进行处理。

在Transact-SQL中进行错误捕捉，如果使用的是SQL Server2005，我的建议是尽量使用try...catch，因此它会捕捉到未预料到的错误，并且会使Transact-SQL更容易维护。当然，这样做就无法将Transact-SQL移植到SQL Server2000或更低的版本上运行，要是想写通用的Transact-SQL，还是使用传统的方法捕捉错误吧！ <script type=text/javascript> google_ad_client = "pub-6430022987645146"; google_ad_slot = "5930513112"; google_ad_width = 728; google_ad_height = 15; </script>

 v