在Sqlserver中使用Try Catch

创建错误日志表:

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

巴特西

在Sqlserver中使用Try Catch

最新文章

热门文章