SQL Server 2005 中使用 Try Catch 处理异常

try...catch是sql server 2005/2008令人印象深刻的新特性.提高了开发人员异常处理能力.没有理由不尝试一下try.. catch功能.

* try 块 - 包含可能产生异常的代码或脚本
* catch 块 - 如果try块出现异常,代码处理流将被路由到catch块.在这里你可以处理异常,记录日志等.
sql server中的try catch和c#,java等语言的处理方式一脉相承.这种一致性才是最大的创新之处.

一、sql server 2000中异常处理

create proc usp_accounttransaction 
 
  @accountnum int, 
 
  @amount decimal 
 
as 
 
begin 
 
  begin transaction --beginning a transaction.. 
 
    update mychecking set amount = amount - @amount 
 
      where accountnum = @accountnum 
 
    if @@error != 0 --check @@error variable after each dml statements.. 
 
    begin 
 
      rollback transaction --rollback transaction if error.. 
 
      return 
 
    end 
 
    else 
 
    begin 
 
      update mysavings set amount = amount + @amount 
 
        where accountnum = @accountnum 
 
      if @@error != 0 --check @@error variable after each dml statements.. 
 
      begin 
 
        rollback transaction --rollback transaction if error.. 
 
        return 
 
      end 
 
      else 
 
      begin 
 
        commit transaction --finally, commit the transaction if success.. 
 
        return 
 
      end 
 
    end 
 
end 
 
go 

上面是sql server 2000的一个存储过程,在每个数据库操作之后立即必须检查@@error,进行commit / rollback该事务.
sql server 2000中监测错误,只能通过监测全局遍历 @@error.由于@@error会被下一个数据库操作所覆盖. 所以在每次操作完后必须立即监测.

二、sql server 2005中异常处理

try...catch是sql server 2005提供的更具有可读性的语法.每个开发人员都熟悉这种写法.sql server 2005仍然支持@@error这种用法.

1.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 

2.获得错误信息的函数表:

下面系统函数在catch块有效.可以用来得到更多的错误信息:

函数 描述

error_number() 返回导致运行 catch 块的错误消息的错误号。
error_severity() 返回导致 catch 块运行的错误消息的严重级别
error_state() 返回导致 catch 块运行的错误消息的状态号
error_procedure() 返回出现错误的存储过程名称
error_line() 返回发生错误的行号
error_message() 返回导致 catch 块运行的错误消息的完整文本

简单示例:

begin try 
 
  select getdate() 
 
  select 1/0--evergreen divide by zero example! 
 
end try 
 
begin catch 
 
  select 'there was an error! ' + error_message() 
 
  return 
 
end catch; 

3.try catch回滚/提交事务的示例

alter proc usp_accounttransaction 
 
  @accountnum int, 
 
  @amount decimal 
 
as 
 
begin 
 
  begin try --start the try block.. 
 
    begin transaction -- start the transaction.. 
 
      update mychecking set amount = amount - @amount 
 
        where accountnum = @accountnum 
 
      update mysavings set amount = amount + @amount 
 
        where accountnum = @accountnum 
 
    commit tran -- transaction success! 
 
  end try 
 
  begin catch 
 
    if @@trancount > 0 
 
      rollback tran --rollback in case of error 
 
    -- you can raise error with raiseerror() statement including the details of the exception 
 
    raiserror(error_message(), error_severity(), 1) 
 
  end catch 
 
end 
 
go

三、实例讲解

创建错误日志表:

create table errorlog(errnum int,errsev nvarchar(1000),errstate int,errproc nvarchar(1000),errline int, errmsg nvarchar(2000))

创建错误日志记录存储过程:

create procedure errorlog
as 
   select error_number() as errnum,error_severity()as errsev,error_state() as errstate,error_procedure() as errproc,error_line()as errline,error_message()as errmsg 
   insert 
   into errorlog 
   values(error_number(),error_severity(),error_state(),error_procedure(),error_line(),error_message())
go

写一个存储过程吧!里面使用一下try catch:

use [your_test]
go
/****** object: storedprocedure [dbo].[gettodaybirthday]  
    script date: 05/17/2010 15:38:46 
    author:jinho
    desc:获取当天生日的所有人
    ******/
set ansi_nulls on
go
set quoted_identifier on
go
alter procedure [dbo].[gettodaybirthday]
as
begin try
 declare @today datetime; 
 set @today = getdate();--获取今天的日期
 declare @day varchar(2);
 set @day =replace(day(@today),0,'');
 declare @month varchar(2) ;
 set @month = replace(month(@today),0,'');
 declare @year varchar(4);
 set @year = year(@today);
 select * from dbo.userinfo where replace(day(convert(datetime,birthday )),0,'') =@day and replace(month(convert(datetime,brithday)),0,'')=@month and birthday is not null 

 end try
 begin catch
 errorlog --调用上面的存储过程,保存错误日志
 end catch

说明:error_number(),error_severity(),error_state(),error_procedure(),error_line(),error_message() 这几个函数只能用在catch里面!