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

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 

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 

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; 

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

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