SQL Server异常处理
目录TRY CATCH
简介
TRY CATCH可以很好的处理SQL Server中的异常。要使用TRY CATCH，首先要在BEGIN TRY…END TRY块中放置一组可能导致异常的T-SQL语句，如下所示：
BEGIN TRY
-- 可能导致异常的语句
END TRY
然后在TRY块之后立即使用BEGIN CATCH…END CATCH块：
BEGIN CATCH
-- 处理异常的语句
END CATCH
以下是完整的TRY CATCH结构：
BEGIN TRY
-- 可能导致异常的语句
END TRY
BEGIN CATCH
-- 处理异常的语句
END CATCH
如果TRY块里边的语句完成且没有错误，则CATCH块不会被执行。但是，如果TRY块中有语句导致异常，则将执行CATCH块中的语句。
CATCH块中的函数
在CATCH块中，可以使用以下函数获取发生错误的详细信息：
ERROR_LINE()返回发生异常的行号。ERROR_MESSAGE()返回生成的错误消息的完整内容。ERROR_PROCEDURE()返回发生错误的存储过程或触发器的名称。ERROR_NUMBER()返回发生的错误的编号。ERROR_SEVERITY()返回所发生错误的严重级别。ERROR_STATE()返回发生错误的状态号。
注意，这些函数只能在CATCH块中使用，如果在其他地方使用，所有这些函数将返回NULL。
嵌套的TRY CATCH
可以将TRY CATCH构造嵌套在另一个TRY CATCH构造中。但是，TRY块或CATCH块都可以包含嵌套的TRY CATCH，例如：
BEGIN TRY
--- 可能导致异常的语句
END TRY
BEGIN CATCH
-- 处理异常的语句
BEGIN TRY
--- 嵌套的TRY块
END TRY
BEGIN CATCH
--- 嵌套的CATCH块
END CATCH
END CATCH
简单示例
首先，创建一个名为usp_divide的存储过程，将两个数字相除：
CREATE PROC usp_divide(
@a decimal,
@b decimal,
@c decimal output
) AS
BEGIN
BEGIN TRY
SET @c = @a / @b;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END;
GO
在这个存储过程中，我们将公式放在TRY块中，并在CATCH块中调用CATCH块函数ERROR_*。
然后，调用usp_divide存储过程将10除以2：
DECLARE @r decimal;
EXEC usp_divide 10, 2, @r output;
PRINT @r;
输出：
5
因为TRY块中没有发生异常，所以存储过程在TRY块处完成。
尝试通过调用usp_divide存储过程将20除以0试试：
DECLARE @r2 decimal;
EXEC usp_divide 10, 0, @r2 output;
PRINT @r2;
下图显示了输出：
由于公式导致的除零错误，所以报错后不再执行TRY块后续代码，直接执行CATCH块内的语句，该语句返回错误的详细信息。
在事务中使用TRY CATCH的示例
新建两张表，sale.persons(人员)和sales.deals(交易)用于示例：
CREATE TABLE sales.persons
(
person_id INT PRIMARY KEY IDENTITY,
first_name NVARCHAR(100) NOT NULL,-- 名字
last_name NVARCHAR(100) NOT NULL-- 性别
);
CREATE TABLE sales.deals
(
deal_id INT PRIMARY KEY IDENTITY,
person_id INT NOT NULL, -- 交易人
deal_note NVARCHAR(100), -- 备注
FOREIGN KEY(person_id) REFERENCES sales.persons(person_id)
);
insert into
sales.persons(first_name, last_name)
values
('John','Doe'),
('Jane','Doe');
insert into
sales.deals(person_id, deal_note)
values
(1,'Deal for John Doe');
然后，创建一个名为usp_report_error的新存储过程，该存储过程用于在CATCH块中用于报告错误的详细信息：
CREATE PROC usp_report_error
AS
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE () AS ErrorLine
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_MESSAGE() AS ErrorMessage;
GO
然后，开发一个新的存储过程，从sales.persons表中删除一行：
CREATE PROC usp_delete_person(
@person_id INT
) AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
-- 删除一个人员
DELETE FROM sales.persons
WHERE person_id = @person_id;
-- 如果删除成功，提交事务
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- 如果出了异常则报告错误，展示错误消息
EXEC usp_report_error;
-- 测试事务是否不可提交。
IF (XACT_STATE()) = -1
BEGIN
PRINT N'The transaction is in an uncommittable state.' +
'Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- 测试事务是否可提交。
IF (XACT_STATE()) = 1
BEGIN
PRINT N'The transaction is committable.' +
'Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH
END;
GO
在这个存储过程中，使用XACT_STATE()函数在CATCH块中执行COMMIT TRANSACTION或ROLLBACK TRANSACITION之前检查事务的状态。
然后，调用usp_delete_person存储过程删除id为2的人员：
没有发生异常。
最后，调用存储过程usp_delete_person删除id为1的人员：
EXEC usp_delete_person 1;
发生以下错误(因为人员1被交易表中的记录引用着，所以报错)：
THROW
简介
THROW语句用于抛出异常，并将执行传输到TRY CATCH的CATCH块。
语法：
THROW [error_number,message ,state ];
其中：
error_number
error_number是一个代表异常的整数。error_number必须大于50000且小于或等于2147483647。
message
message是一个描述异常的NVARCHAR(2048)类型的字符串。
state
state是一个TINYINT，其值介于0和255之间。状态表示与消息关联的状态。
如果没有为THROW语句指定任何参数，则必须将THROW声明放在CATCH块中：
BEGIN TRY
-- 可能导致异常的语句
END TRY
BEGIN CATCH
-- 处理异常的语句
THROW;
END CATCH
在这种情况下，THROW语句将CATCH块捕获的错误再抛出。
请注意，THROW语句之前的语句必须以分号(;)结束
示例
A) 以下示例使用THROW语句引发异常：
THROW 50005, N'An error occurred', 1;
输出：
Msg 50005, Level 16, State 1, Line 1
An error occurred
B）使用THROW语句重新引发异常
首先，为演示创建一个新表t1：
CREATE TABLE t1(
id int primary key
);
GO
然后，使用CATCH块中不带参数的THROW语句重新抛出捕获的错误：
BEGIN TRY
INSERT INTO t1(id) VALUES(1);
-- cause error
INSERT INTO t1(id) VALUES(1);
END TRY
BEGIN CATCH
PRINT('Raise the caught error again');
THROW;
END CATCH
输出：
(1 row affected)
(0 rows affected)
Raise the caught error again
Msg 2627, Level 14, State 1, Line 10
Violation of PRIMARY KEY constraint 'PK__t1__3213E83F906A55AA'. Cannot insert duplicate key in object 'dbo.t1'. The duplicate key value is (1).
在这个示例中，第一条INSERT语句成功了。但是，由于主键约束，第二个失败。因此，THROW语句再次引发CATCH块捕获的错误。
C）使用FORMATMESSAGE函数
与RAISERROR语句不同，THROW语句不允许您替换消息文本中的参数。因此，要模拟此功能，可以使用FORMATMESSAGE()函数。
以下语句将自定义消息添加到sys.messages目录视图中：
EXEC sys.sp_addmessage
@msgnum = 50010,
@severity = 16,
@msgtext =
N'The order number %s cannot be deleted because it does not exist.',
@lang = 'us_english';
GO
此语句使用message_id 50010并将%s占位符替换为订单id '1001'：
DECLARE @MessageText NVARCHAR(2048);
SET @MessageText = FORMATMESSAGE(50010, N'1001');
THROW 50010, @MessageText, 1;
输出：
Msg 50010, Level 16, State 1, Line 8
The order number 1001 cannot be deleted because it does not exist.