SQL Server 2005 中使用 Try Catch 处理异常（sql server2014使用教程）学到了

随心笔谈2年前发布编辑

129 0

🌐 经济型：买域名、轻量云服务器、用途：游戏网站等《腾讯云》特点：特价机便宜适合初学者用点我优惠购买

🚀 拓展型：买域名、轻量云服务器、用途：游戏网站等《阿里云》特点：中档服务器便宜域名备案事多点我优惠购买

🛡️ 稳定型：买域名、轻量云服务器、用途：游戏网站等《西部数码》特点：比上两家略贵但是稳定性超好事也少点我优惠购买

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

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

上面是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

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

三、实例讲解

创建错误日志表:

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

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

您可能感兴趣的文章:在SQL Server的try…catch语句中获取错误消息代码的的语句SQL SERVER 里的错误处理（try catch)在 SQL Server 中使用 Try Catch 处理异常