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
