在SqlServer中使⽤TryCatch（转）
今天在写sql中出现了！我想在sql使⽤trycatch吧! 哎..但是语法⼜记不住了! 那就清楚我们的google⼤师吧! 嘿，⽹上关于在sql中使⽤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 --调⽤上⾯的存储过程，保存错误⽇志
server error啥意思END CATCH
说明:ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE() 这⼏个函数只能⽤在Catch⾥⾯!