对SQL Server 2005应用TRY...CATCH回传事务

本文英文原版：
http://www.4guysfromrolla.com/webtech/041906-1.shtml

对SQL Server 2005应用TRY...CATCH回传事务

导言：

SQL Server 2005相比以前的老版本提供了一些新的特性.本文我们将注意力集中在SQL Server 2005所支持的TRY...CATCH模块.它是现代语言处理异常的标准方法，包括：

.一个TRY模块—该模块用来包含可能引发异常的指令
.一个CATCH模块—如果在TRY模块发生异常的话，程序控制将转到CATCH模块处理异常.

关于exception handling的概念以及TRY...CATCH构造器的更多信息请参阅文章《Exception Handling》

在SQL Server 2005之前，要检查返回的T-SQL statement是否出错，只能检查一个全局的error变量@@ERROR.由于每执行一条SQL statement后，都会对@@ERROR进行重置，在每个statement执行后对该变量的检查将会使储过程显的臃肿.

SQL Server 2005支持的TRY...CATCH模块提供了一种易读性更强、开发者更熟悉的方法来进行处理.本文，我们将看到在出现问题时如何运用TRY...CATCH模块来回滚事务.

检查@@ERROR值——T-SQL里处理Errors的一种旧方法

SQL Server用@@ERROR变量来指示刚执行的SQL statement的状态。如果执行完全成功，那么值为0，如果发生了错误，那么@@ERROR就被设为error message的条数.

@@ERROR变量是怎么使用的呢？假定我们有一个数据驱动web应用程序，它包含员工信息.假设在数据库里有Employees 和 EmployeePhoneNumbers以及其它的table.这2个表的关系为一对多；也就是说每一条Employees记录在EmployeePhoneNumbers表里有相应的任意多条记录，比如可能是办公室号码、呼机号码等.假定我们有一个名为DeleteEmployee的存储过程，它有2条DELETE statements，一个是删除员工的相关电话号码，而另一个是删除实际的员工记录：

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int )
AS

-- Delete the Employee's phone numbers
DELETE FROM EmployeePhoneNumbers
WHERE EmployeeID = @EmployeeID

-- Delete the Employee record
DELETE FROM Employees
WHERE EmployeeID = @EmployeeID

因为我们想让这2条delete statement同时执行成功或失败，因此我们要将这2个statements封装到一个事务里.利用事务，在出现异常时我们便可以回滚事务，因而不会对数据库进行改动.为此，我们最开始可能要使用这样的语法：

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int )
AS

BEGIN TRANSACTION -- Start the transaction

-- Delete the Employee's phone numbers
DELETE FROM EmployeePhoneNumbers
WHERE EmployeeID = @EmployeeID

-- Delete the Employee record
DELETE FROM Employees
WHERE EmployeeID = @EmployeeID

-- See if there is an error
IF @@ERROR <> 0
-- There's an error b/c @ERROR is not 0, rollback
ROLLBACK
ELSE
COMMIT -- Success! Commit the transaction

该存储过程(似乎)开启了一个事务，运行这2个DELETE statement，再检查看是否有异常，如果有哪怕一异常，它都会回滚事务，如果没有那就提交事务.我说“似乎”，那是因为严格来说，该语法在语义上是不正确的，因为每执行一个DELETE statement后都会重置@@ERROR变量的值.因此，如果第一个DELETE statement有异常的话，那么@@ERROR变量的值就会重置为其error的条数.然后再执行第二个DELETE.如果第二个DELETE执行成功，那么@@ERROR的值将会设置为0，在这种情况下，就算第一个statement有问题也会提交事务！多么可怕！

因此，我们必须在每一个SQL statement执行后进行检查，看是否有错误.如果有则回滚事务并退出存储过程.这样的话就会使存储过程变得臃肿，比如，对5个statement就要检查5次@@ERROR变量.如果你漏掉了某条statement的检查那么就会出现潜在的问题.

关于事务和@@ERROR语法的更多信息，请参阅文章《Managing Transactions in SQL Server Stored Procedures》

用SQL Server 2005的TRY...CATCH模块处理Errors

虽然SQL Server 2005依然支持@@ERROR方法，但还有一个更好的处理方法，那就是新特性TRY...CATCH模块.就像程序语言Visual Basic, C#,以及Java一样，SQL Server 2005的TRY...CATCH模块在TRY模块里执行一系列的statement,如果执行这些statement都没有问题那么程序就跳过CATCH模块.相反，如果有异常那么程序就转入CATCH模块。此外，类似程序设计语言，TRY...CATCH模块允许进行镶套，那就是说在外部TRY...CATCH模块的TRY 或 CATCH里我们可以再镶套一个完整的TRY...CATCH模块.

BEGIN TRY
Try Statement 1
Try Statement 2
...
Try Statement M
END TRY
BEGIN CATCH
Catch Statement 1
Catch Statement 2
...
Catch Statement N
END CATCH

以下是在CATCH模块里可以用到的系统函数，我们可以用它来探测错误信息：

我们应该意识到并非TRY模块里statement引发的所有的错误都会使程序控制转到CATCH模块.那些安全级别为10及以下的错误会被判断为“警告”级别的错误，因而不会转入CATCH模块.另外有关数据库连接的错误也不会导致转入CATCH模块.

让我们快速的看一个TRY...CATCH的例子，然后我们将注意力转移到出现异常时用TRY...CATCH回滚事务的情况.下面的示例将向Northwind数据库的Products表添加一个简单的INSERT查询. 由于Products表的ProductID列是一个值自增列(IDENTITY column)，因此当添加一个新记录时不能指定该列的值，不过我在下面的INSERT statement里指定了该列的值.因此，程序控制将转入CATCH模块，并将错误信息展示出来.

BEGIN TRY
-- This will generate an error, as ProductID is an IDENTITY column
-- Ergo, we can't specify a value for this column...
INSERT INTO Products(ProductID, ProductName)
VALUES(1, 'Test')
END TRY
BEGIN CATCH
SELECT 'There was an error! ' + ERROR_MESSAGE()
END CATCH

这样的话将返回一个单列的记录，内容为："There was an error! Cannot insert explicit value for identity column in table 'Products' when IDENTITY_INSERT is set to OFF."

出现异常时，使用TRY...CATCH来回滚事务

正如在本文前面所探讨的那样， 在事务里的每一个SQL statement之后都应该用一个@@ERROR变量来探测是否发生了异常，如果发生了那就回滚事务.不过用SQL Server 2005的TRY...CATCH模块的话就可以极大的进行简化，如下所示：

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int )
AS

BEGIN TRY
BEGIN TRANSACTION -- Start the transaction

-- Delete the Employee's phone numbers
DELETE FROM EmployeePhoneNumbers
WHERE EmployeeID = @EmployeeID

-- Delete the Employee record
DELETE FROM Employees
WHERE EmployeeID = @EmployeeID

-- If we reach here, success!
COMMIT
END TRY
BEGIN CATCH
-- Whoops, there was an error
IF @@TRANCOUNT > 0
ROLLBACK

-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

在TRY模块，开启一个事务并执行2个DELETE statements.如果这2个DELETE执行成功那么就用COMMIT来提交事务.如果有异常，那么程序控制就会转入CATCH模块，回滚事务，此外，CATCH模块还使用RAISERROR来再次引发错误，这样该error信息就会传递到调用该存储过程的应用程序.对ASP.NET web应用程序而言，这就意味着，在调用该存储过程的.NET代码处引发一个异常，我们不仅希望在此回传事务而且还希望在web应用程序处理一些出错信息以告知终端用户他们的操作失败了.

在CATCH模块里调用RAISERROR的效果等同于在编程语言的TRY...CATCH模块的CATCH里抛出一个异常.上面示例的最终结果将回滚事务.如果您忽略了RAISERROR,那么在执行数据库命令时，ASP.NET应用程序就不会抛出一个异常.使用RAISERROR的话你就可以在ASP.NET里抛出异常(不然的话，如果操作失败了你还不知道是怎么回事情).

结语：

SQL Server 2005所支持的TRY...CATCH模块将我们熟悉的TRY...CATCH处理方式引入到T-SQL里.在2005之前的版本里，要探测错误只能通过使用@@ERROR变量.在每执行一条SQL statement之后都要对它进行检查，很烦人.这将导致代码臃肿且在复制、粘贴的时候很容易出问题，而导致潜在的重大威胁.有了SQL Server 2005的TRY...CATCH模块，那就是另一番光景了。任何时候TRY模块里的任何一条statement所引发的错误都会使程序控制转入CATCH模块，正如我们在本文所看到的那样，TRY...CATCH提供了更易懂更简洁的处理方式.

祝编程愉快！