在SQL Server 2005数据库中进行错误捕捉

在SQL Server数据库中，如果执行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 Server 2005数据库中错误捕捉的新功能

　　虽然在以前的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就不会抛出任何错误，只是返回了一个错误码，这种做法的好处是有利于客户端代码进行处理。

　　如果使用SQL Server 2005数据库，在Transact-SQL中进行错误捕捉，建议尽量使用try...catch，因为它会捕捉到未预料到的错误，并且会使 Transact-SQL更易于维护。当然，这样做就无法将Transact-SQL移植到SQL Server2000或更低的版本上运行，如果想写通用的Transact-SQL，读者可以使用传统的方法来捕捉错误。