1 TRY…CATCH
1.1 用法
TRY…CATCH 的语法如下 :
BEGIN TRY -- TRY 模块
-- 业务处理
END TRY
BEGIN CATCH -- CATCH 模块
-- 错误处理
END CATCH 在 BEGIN TRY 和 END TRY 中的是 TRY 模块； BEGIN CATCH 和 END CATCH 之间是 CATCH 模块。 TRY 模块中的任何一条 T-SQL 出错时，将跳到 CATCH 模块（ TRY 模块中，出错的 T-SQL 后的语句不会被执行）。
1.2 错误处理函数
使用 TRY…CATCH 时，必须在 CATCH 模块中对错误处理处理，如果不处理，则 SQL Server 不会给出任何提示，这样就不会知道是否有错误发生。
在 CATCH 模块中，可以使用下面的这些函数来实现错误处理（这些函数只能用在 CATCH 模块中），在其他位置使用时，这些函数返回 NULL 值。
n ERROR_NUMBER() 返回错误号
n ERROR_MESSAGE() 返回错误消息的完整文本。此文本包括为任何可替换参数（如长度、对象名或时间）提供的值
n ERROR_SEVERITY() 返回错误严重性
n ERROR_STATE() 返回错误状态号
n ERROR_LINE() 返回导致错误的例程中的行号
n ERROR_PROCEDURE() 返回出现错误的存储过程或触发器的名称
1.3 使用 TRY…CATCH 时的注意事项
使用 TRY…CATCH 时，需要注意下述事项：
n CATCH 模块必须紧跟在 TRY 模块之后
n TRY…CATCH 构造可以嵌套。这意味着可以将 TRY…CATCH 构造放置在其他 TRY 模拟和 CATCH 模块内。当嵌套的 TRY 块中出现错误时，程序控制将传递到与嵌套的 TRY 块关联的 CATCH 块
n 严重性为 10 或更低的错误被视为警告或信息性消息，这种错误不会导致处理跳到 CATCH 模块（通过 RAISERROR 抛出的自定义错误同样适用于此规则）。参考下面的 T-SQL 代码段进行测试。
BEGIN TRY
-- 业务处理
RAISERROR('start', 10, 1) -- 此句不会导致处理转到CATCH 模块
RAISERROR('warning', 11, 1) -- 此句会导致处理转到CATCH 模块
RAISERROR('finish', 10, 1) -- 由于上一句导致处理转到CATCH 模块, 故此句不会被执行
END TRY
BEGIN CATCH
-- 错误处理
SELECT
ERROR_MESSAGE() -- 返回warning
END CATCH 2 TRY…CATCH and Transaction
2.1 XACT_STATE() 函数
一般在 TRY…CATCH 中使用事务时，会在 TRY 模块提交（ COMMIT ）事务；而在 CATCH 块回滚（ ROLLBACK ）事务。
如果要准确的控制事务的提交和回滚，则可以通过 XACT_STATE() 函数判断当前事务的状态，以进一步确定如何进行事务处理。
XACT_STATE() 函数在 SQL Server 2005 及其之后的版本中可用，该函数返回下列值：
n 0 当前请求没有活动的用户事务
n 1 当前请求有活动的用户事务。请求可以执行任何操作，包括写入数据和提交事务
n -1 当前请求具有活动的用户事务，但法提交事务或回滚到保存点；它只能请求完全回滚事务
2.2 嵌套事务与事务保存点
SQL Server 中的事务处理可以嵌套。可以通过全局变量 @@TRANCOUNT 查询当前连接的活动事务数（也就是事务嵌套的层数）。如果该变量值为 0 ，则表示当前连接没有启用任何事务；如果该变量值 >1 ，则表示当前连接存在事务嵌套。
SQL Server 自动维护全局变量 @@TRANCOUNT 的值。当使用 BEGIN TRANSACTION 语句时， SQL Server 将 @@TRANCOUNT 加 1 ；使用 COMMIT TRANSACTION 或 COMMIT WORK 时， SQL Server 将 @@TRANCOUNT 递减 1 ；而使用 ROLLBACK TRANSACTION 时（不包括使用 ROLLBACK TRANSACTION savepoint_name 的情况），所有的事务都被回滚，对应的， @@TRANCOUNT 直接递减为 0 。
嵌套事务一般出现在嵌套存储过程时，但一般不建议使用嵌套事务，这会增加事务控制的难度。一般建议的事务处理方法，是在可能出现嵌套事务的地方，通过判断 @@TRANCOUNT 的值来确定当前的事务数，如果当前没有事务，则开启新的事务；如果已经有事务，则使用 SAVE TRANSACTION savepoint_name 语句设置事务保存点，以便在需要回滚当前处理的时候，可以通过 ROLLBACK TRANSACTION savepoint_name 语句将事务回滚到保存点。
3 Template
下面是一个涉及 TRY…CATCH 和事务处理的模板。可以参考这个模板来编写业务存储过程，如果业务处理不涉及事务，则可以去掉事务处理的那些部分。
CREATE PROC procedure_name
AS
SET NOCOUNT ON
-- 当前的事务数
DECLARE
@trancount int
SELECT
@trancount = @@TRANCOUNT
-- TRY...CATCH 处理
BEGIN TRY
-- ========================================
-- 标准的事务处理模块块
-- a. 开启事务, 或者设置事务保存点
IF @trancount = 0
BEGIN TRAN
ELSE
SAVE TRAN TRAN_SavePoint
-- b. 这里放置处于事务中的各种处理语句
-- c. 提交事务
-- 有可提交的事务, 并且事务是在当前模块中开启的情况下, 才提交事务
IF XACT_STATE() = 1 AND @trancount = 0
COMMIT
-- ========================================
-- ========================================
-- 为了防止TRY 中有遗漏的事务处理, 可以在TRY 模拟的结束部分做最终的判断
IF @trancount = 0
BEGIN
IF XACT_STATE() = -1
ROLLBACK TRAN
ELSE
BEGIN
WHILE @@TRANCOUNT > 0
COMMIT TRAN
END
END
END TRY
BEGIN CATCH
-- ========================================
-- 在CATCH 模块, 应该首先处理事务
IF XACT_STATE() <> 0
BEGIN
IF @trancount = 0
ROLLBACK TRAN
-- XACT_STATE 为-1 时, 不能回滚到事务保存点, 这种情况留给外层调用者做统一的事务回滚
-- 通过@@TRANCOUNT > @trancount 的判断, 即使在TRY 模块中没有设置事务保存点的情况下跳到此步骤, 也不会出错
ELSE IF XACT_STATE() = 1 AND @@TRANCOUNT > @trancount
ROLLBACK TRAN TRAN_SavePoint
END
-- ========================================
-- 错误消息处理
-- a. 获取错误信息
-- 这提提取了错误相关的全部信息, 可以根据实际需要调整
DECLARE
@error_number int,
@error_message nvarchar(2048),
@error_severity int,
@error_state int,
@error_line int,
@error_procedure nvarchar(126),
@user_name nvarchar(128),
@host_name nvarchar(128)
SELECT
@error_number = ERROR_NUMBER(),
@error_message = ERROR_MESSAGE(),
@error_severity = ERROR_SEVERITY(),
@error_state = ERROR_STATE(),
@error_line = ERROR_LINE(),
@error_procedure = ERROR_PROCEDURE(),
@user_name = SUSER_SNAME(),
@host_name = HOST_NAME()
-- b. 对于重要的业务处理存储过程, 应该考虑把错误记录到表中备查(这个表需要先建立)
-- 记录错误应该在没有事务的情况下进行了, 否则可能因为外层事务的影响导致保存失败
IF XACT_STATE() = 0
INSERT dbo.tb_ErrorLog(
error_number,
error_message,
error_severity,
error_state,
error_line,
error_procedure,
user_name,
host_name,
indate)
VALUES(
@error_number,
@error_message,
@error_severity,
@error_state,
@error_line,
@error_procedure,
@user_name,
@host_name,
GETDATE())
-- c. 如果没有打算在CATCH 模块中对错误进行处理, 则应该抛出错误给调用者
-- 注: 不允许在被SSB 调用的存储过程中, 将错误或者其他信息抛出
-- 因为SSB 是自动工作的, 如果它调用的存储过程有抛出信息, 则这个信息会被直接记录到SQL Server 系统日志
-- 而目前SSB 的消息数量是很多的, 这会导致SQL Server 日志爆涨掉
-- 对于被SSB 调用的存储过程, 应该在CATCH 模块中加入自己的错误处理(最简单的就是将错误记录到表中)
RAISERROR(
N'User: %s, Host: %s, Procedure: %s, Error %d, Level %d, State %d, Line %d, Message: %s ',
@error_severity,
1,
@user_name,
@host_name,
@error_procedure,
@error_number,
@error_severity,
@error_state,
@error_line,
@error_message)
END CATCH
GO 4 Policy or suggest
在使用 TRY…CATCH 和事务处理时，我们有如下的一些规范需要遵守：
n 禁止 在发布到 production 的存储过程中抛出警告或信息性消息，这包含使用 PRINT 语句和使用 RAISERROR 语句触发严重性为 10 或更低的错误
n 使用 TRY…CATCH 时， CATCH 块中 必须 进行错误处理
n 对于重要的业务存储过程， 必须 建立对应的错误日志记录表，将 CATCH 查看更多关于SQL2005trycatch的详细介绍的详细内容...