SQL Server中使用异常处理调试存储过程(转)

异常处理被普遍认为是T-SQL脚本编程中的最弱的方面。幸运的是，这一点在SQL Server 2005中得到了改变，因为SQL Server 2005支持结构化异常处理。本文首先关注新特性“TRY……CATCH”的基本构成，然后在SQL Server 2000和SQL Server 2005中对照着看一些T-SQL的例子，这些例子中使用事务代码故意制造了一些违反约束限制的情况。将来的文章会继续探讨这一主题。

　　在SQL Server之前的版本中，你需要在执行INSERT，UPDATE，DELETE之后立即检查全局变量“@@error”来处理异常，如果“@@error”变量不为零的话（表示有错误），就接着执行一些纠正动作。开发人员常常重复这种与业务逻辑无关的代码，这会导致重复代码块，而且需要与GOTO语句和RETURN语句结合使用。

　　结构化异常处理为控制具有许多动态运行时特性的复杂程序提供了一种强有力的处理机制。目前，这种机制经实践证明是良好的，许多流行的编程语言（比如：微软的Visual Basic.Net和Visual C#）都支持这种异常处理机制。接下来你会在例子中看到，采用了这种健壮的方法以后，会使你的代码可读性和可维护性更好。TRY块包含了可能潜在失败的事务性代码，而CATCH块包含了TRY块中出现错误时执行的代码。如果TRY块中出现了任何错误，执行流程被调转到CATCH块，错误可以被处理，而出错函数可以被用来提供详细的错误信息。TRY……CATCH基本语法如下：

BEGIN TRY
　　RAISERROR （'Houston, we have a problem', 16,1）
　　END TRY
　　BEGIN CATCH
　　SELECT ERROR_NUMBER（） as ERROR_NUMBER,
　　ERROR_SEVERITY（） as ERROR_SEVERITY,
　　ERROR_STATE（） as ERROR_STATE,
　　ERROR_MESSAGE（） as ERROR_MESSAGE
　　END CATCH

注意上面脚本中函数的用法，我们可以用它们代替局部变量和（或者）全局变量。这些函数只应该被用在CATCH块中，函数功能说明如下：

　　ERROR_NUMBER（） 返回错误数量。

　　ERROR_SEVERITY（） 返回错误严重等级。

　　ERROR_STATE（） 返回错误状态号。

　　ERROR_PROCEDURE（） 返回出错位置存储过程或者触发器的名称。

　　ERROR_LINE（） 返回程序中引起错误的行号。

　　ERROR_MESSAGE（） 返回错误信息的完整文本。错误内容包括可替换参数的值，比如：长度，对象名称或者时间。

　　我会先用SQL Server 2000演示一个简单例子，然后演示一个SQL Server 2005异常处理的例子。

　　下面是一个简单的存储过程示例，先用SQL Server 2000编写，然后改用SQL Server 2005实现。两者都从简单的表开始，我们在对这些表执行插入操作时会违反约束限制。下面是表结构：

create table dbo.Titles
　　（TitleID int Primary Key identity,
　　TitleName nvarchar（128） NOT NULL,
　　Price money NULL constraint CHK_Price check （Price > 0））
　　create table dbo.Authors
　　（Authors_ID int primary key identity,
　　au_fname nvarchar（32） NULL,
　　au_lname nvarchar（64） NULL,
　　TitleID int constraint FK_TitleID foreign key
　　references Titles（TitleID）,
　　CommissionRating int constraint CHK_ValidateCommissionRating
　　Check （CommissionRating between 0 and 100））
　　create table dbo.Application_Error_Log
　　（tablename sysname,
　　userName sysname,
　　errorNumber int,
　　errorSeverity int,
　　errorState int,
　　errorMessage varchar（4000））

存储过程P_Insert_New_BookTitle_2K的源代码

Code
----------------------------------------------

P_Insert_New_BookTitle_2K

----------------------------------------------

create proc P_Insert_New_BookTitle_2K
(@TitleName nvarchar(128),
 @Price money,
 @au_fname nvarchar(32),
 @au_name nvarchar(64),
 @CommissionRating int)
as

declare     @err int,
        @tablename sysname

begin transaction

insert dbo.Titles (TitleName, Price)
values (@TitleName, @Price)

select @err = @@error
if @err <> 0
begin
    select @tablename = 'titles'
    GOTO ERROR_HANDLER
end

insert dbo.Authors  (au_fname, au_lname, TitleID, CommissionRating)
values (@au_fname, @au_fname, @@IDENTITY, @CommissionRating)

if @err <> 0
begin
    select @tablename = 'authhors'
    GOTO ERROR_HANDLER
end

GOTO EXIT_Proc


ERROR_HANDLER:

ROLLBACK TRANSACTION

-- Log the error 
insert dbo.Application_Error_Log (tableName, UserName, errorNumber, errorSeverity, errorState)
values (@tableName, suser_sname(), @err, 0, 0)

EXIT_Proc:

commit tran


----------------------------------------------

你可以看到，这个存储过程包含了非结构化的错误处理代码，这是我们在SQL Server 2005之前使用的方式。

　　我们已经先看到了存储过程P_Insert_New_BookTitle_2K中使用的代码。你顶多能说：“至少我们有异常处理。”下面的语句执行这个SQL Server 2000下的存储过程。

Code
exec P_Insert_New_BookTitle_2K 'Red Storm Rising',16.99,
　　'Tom','Clancy', 200

在用指定的参数执行存储过程时，对Authors表的插入失败了，因为佣金费率值无效。我们的约束检查发现了该无效值，我们可以看到如下错误信息：

Code
Msg 547, Level 16, State 0, Procedure P_Insert_New_BookTitle, Line 23 The INSERT statement conflicted with the CHECK constraint "CHK_ValidateCommissionRating". The conflict occurred in database "Adventureworks2005", table "dbo.Authors", column 'CommissionRating'. The statement has been terminated.

这里的问题是我们不能阻止这些消息被送到客户端。所以判断哪里出错的重担就放到了客户端的头上。令人遗憾的是，在有些情况下，这样的结果对于一些不使用约束限制的应用程序可能足够了。

　　我们再来试一次，这次我们使用TRY……CATCH代码块。

存储过程P_Insert_New_BookTitle_2K5的源代码

Code
----------------------------------------------

----------------------------------------------

P_Insert_New_BookTitle_2K5 

----------------------------------------------


create proc P_Insert_New_BookTitle_2K5
(@TitleName nvarchar(128),
 @Price money,
 @au_fname nvarchar(32),
 @au_name nvarchar(64),
 @CommissionRating int)
as
declare @err int,
		@tablename sysname,
		@errormessage nvarchar(2000)

BEGIN TRY

	begin transaction

	select @errormessage = 'insert into Titles table failed',
		 @tablename = 'Titles'
	insert dbo.Titles (TitleName, Price)
	values (@TitleName, @Price)

	select @errormessage = 'insert into Authors table failed',
		 @tablename = 'Authors'
insert dbo.Authors  (au_fname, au_lname, TitleID, 
                     CommissionRating)
values (@au_fname, @au_fname, @@IDENTITY, 
        @CommissionRating)

	commit transaction

END TRY


BEGIN CATCH
	
	ROLLBACK TRANSACTION

	-- Log the error 
	insert dbo.Application_Error_Log (UserName, tableName,     
            errorNumber, errorSeverity, errorState, errorMessage)
	values (suser_sname(), @tableName, ERROR_NUMBER(),  
            ERROR_SEVERITY(), ERROR_STATE(), ERROR_MESSAGE())  

	RAISERROR (@errormessage, 16,1)

END CATCH



----------------------------------------------

在这段新改进的存储过程中，我们看到使用了TRY……CATCH代码块的结构化错误处理：

　　要注意SQL Server 2005异常处理代码是经过简化的，因此具有更好的可读性和可维护性。不需要剪切和粘贴异常处理代码，也不需要使用GOTO语句。执行该存储过程时，你可以看到如下结果：

exec P_Insert_New_BookTitle_2K5 'Red Storm Rising',16.99,
　　'Tom','Clancy', 200
我们用指定的参数执行存储过程，同样因为佣金费率值无效，对Authors表的插入失败了。错误发生时，程序执行流程跳转到了CATCH代码块，在CATCH代码块中我们回滚了事务，然后用SQL Server 2005自带的函数给Application_Error_Log表插入一行日志。

　　新的TRY……CATCH代码块无疑使编写处理错误代码更容易，它还可以在任何时候阻止错误信息发送到客户端。当然这可能需要T-SQL程序员的编程思维有一个转变，这是一个绝对有必要使用的特性。要记住迁移SQL Server 2000代码到SQL Server 2005时，如果程序的错误处理机制已经设计为旧的发送错误到客户端的方式，那你可能不得不修改应用程序了。从长远来看，我相信为这种潜在的问题付出努力重新设计是值得的。

 备注：
http://www.searchdatabase.com.cn/ShowContent_23265.htm

转载于:https://www.cnblogs.com/kevinlzf/archive/2009/08/20/1550344.html