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里面!