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值。
- ERROR_NUMBER() 返回错误号
- ERROR_MESSAGE() 返回错误消息的完整文本。此文本包括为任何可替换参数（如长度、对象名或时间）提供的值
- ERROR_SEVERITY() 返回错误严重性
- ERROR_STATE() 返回错误状态号
- ERROR_LINE() 返回导致错误的例程中的行号
- ERROR_PROCEDURE() 返回出现错误的存储过程或触发器的名称
1.3 使用TRY…CATCH时的注意事项
使用TRY…CATCH时，需要注意下述事项：
- CATCH模块必须紧跟在TRY模块之后
- TRY…CATCH构造可以嵌套。这意味着可以将TRY…CATCH构造放置在其他TRY模拟和CATCH模块内。当嵌套的TRY块中出现错误时，程序控制将传递到与嵌套的TRY块关联的CATCH块
- 严重性为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及其之后的版本中可用，该函数返回下列值：
- 0 当前请求没有活动的用户事务
- 1 当前请求有活动的用户事务。请求可以执行任何操作，包括写入数据和提交事务
- -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和事务处理时，我们有如下的一些规范需要遵守：
- 禁止在发布到production的存储过程中抛出警告或信息性消息，这包含使用PRINT语句和使用RAISERROR语句触发严重性为10或更低的错误
- 使用TRY…CATCH时，CATCH块中必须进行错误处理
- 对于重要的业务存储过程，必须建立对应的错误日志记录表，将CATCH块捕获的错误记录到错误日志记录表中
- 对于被DBA的SSB存储过程自动调用的存储过程，禁止抛出任何信息，所有的错误应该在被调用的存储过程中完成。虽然被调用的存储过程发生错误可以被DBA的SSB存储过程捕获，但由于涉及业务处理，DBA无法修复这些错误，故这些错误不应该给出DBA。而对于抛出的信息，这个不会被捕获，但由于是SSB自动调用的，因此会写入SQL Server的日志中，这可能会产生大量的日志，导致磁盘空间紧张，从而影响SQL Server运行
- 在嵌套调用的存储过程的时候，建议对存储过程的返回值做判断，以确定被调用的存储过程是否出现过问题。要获取存储过程的返回值，可以使用类似下面的调用方法（除非使用RETURN 语句手工设置返回值，否则无论被调用的存储过程中产生的错误是否被处理，只要有错误产生，返回的值都不会是0）
DECLARE @__sp_re int EXEC @__sp_re = dbo.procedure_name IF @__sp_re <> 0 RAISERROR('has some error', 16, 1)
5 Reference
Reference Document
TRY...CATCH
URL: http://msdn2.microsoft.com/en-us/library/ms175976.aspx
SAVE TRANSACTION