Transact-SQL 代码中的错误可使用 TRY…CATCH 构造处理,此功能类似于C#语言的异常处理功能。TRY…CATCH 构造包括两部分:一个 TRY 块和一个 CATCH 块
1.TRY...CATCH 块不处理导致数据库引擎关闭连接的严重性为 20 或更高的错误。但是,只要连接不关闭,TRY...CATCH 就会处理严重性为 20 或更高的错误。
2.严重性为 10 或更低的错误被视为警告或信息性消息,TRY...CATCH 块不处理此类错误。
对于与TRY...CATCH 构造在同一执行级别发生的错误,TRY...CATCH 将不处理以下两类错误:
1.编译错误,例如阻止批处理执行的语法错误。
2.语句级重新编译过程中出现的错误,例如由于名称解析延迟而造成在编译后出现对象名解析错误。
![](/Images/OutliningIndicators/ContractedBlock.gif)
Code
1
--由SELECT 语句生成的对象名解析错误是不被TRY…CATCH 构造捕捉
2
BEGIN TRY
3
-- Table does not exist
4
SELECT * FROM Table1;
5
END TRY
6
BEGIN CATCH
7
SELECT
8
ERROR_NUMBER() AS ErrorNumber,
9
ERROR_SEVERITY() AS ErrorSeverity,
10
ERROR_STATE() AS ErrorState,
11
ERROR_PROCEDURE() AS ErrorProcedure,
12
ERROR_LINE() AS ErrorLine,
13
ERROR_MESSAGE() AS ErrorMessage;
14
END CATCH
15![](/Images/OutliningIndicators/None.gif)
如果某个错误在 TRY 块内的编写或语句级别重新编写过程中并在较低的执行级别(例如,执行 sp_executesql 或用户定义存储过程时)发生,则该错误会在低于 TRY…CATCH 构造的级别上发生,并由相关联的 CATCH 块处理。
![](/Images/OutliningIndicators/ContractedBlock.gif)
Code
1
CREATE PROCEDURE pr_1
2
AS
3
-- Table does not exist
4
SELECT * FROM Table1;
5
GO
6![](/Images/OutliningIndicators/None.gif)
7
BEGIN TRY
8
EXECUTE pr_1
9
END TRY
10
BEGIN CATCH
11
SELECT
12
ERROR_NUMBER() AS ErrorNumber,
13
ERROR_SEVERITY() AS ErrorSeverity,
14
ERROR_STATE() AS ErrorState,
15
ERROR_PROCEDURE() AS ErrorProcedure,
16
ERROR_LINE() AS ErrorLine,
17
ERROR_MESSAGE() AS ErrorMessage;
18
END CATCH;
19![](/Images/OutliningIndicators/None.gif)
2007年12月3日13:53:38
![](/Images/OutliningIndicators/ContractedBlock.gif)
Code
1
--ids 主键
2
create table test1( ids int not null,
3
constraint pk_test1 primary key(ids)
4
)
5
go
6
select * from test1
7
begin try
8![](/Images/OutliningIndicators/None.gif)
9
BEGIN TRAN
10
insert into test1 (ids)
11
select 1
12![](/Images/OutliningIndicators/None.gif)
13
insert into test1 (ids)
14
select 2
15![](/Images/OutliningIndicators/None.gif)
16
insert into test1 (ids)
17
select 1 --违反了主键约束
18![](/Images/OutliningIndicators/None.gif)
19
COMMIT TRAN
20
PRINT 'Transaction committed'
21
end try
22
begin catch
23
ROLLBACK
24
PRINT 'Transaction rolled back'
25![](/Images/OutliningIndicators/None.gif)
26
SELECT
27
ERROR_NUMBER() AS ErrorNumber,
28
ERROR_SEVERITY() AS ErrorSeverity,
29
ERROR_STATE() AS ErrorState,
30
ERROR_PROCEDURE() AS ErrorProcedure,
31
ERROR_LINE() AS ErrorLine,
32
ERROR_MESSAGE() AS ErrorMessage;
33
end catch
34![](/Images/OutliningIndicators/None.gif)
35
select * from test1
36![](/Images/OutliningIndicators/None.gif)
TRY...CATCH 使用下列错误函数来捕获错误信息:
ERROR_NUMBER() 返回错误号。
ERROR_MESSAGE() 返回错误消息的完整文本。此文本包括为任何可替换参数(如长度、对象名或时间)提供的值。
ERROR_SEVERITY() 返回错误严重性。
ERROR_STATE() 返回错误状态号。
ERROR_LINE() 返回导致错误的例程中的行号。
ERROR_PROCEDURE() 返回出现错误的存储过程或触发器的名称。
![](/Images/OutliningIndicators/ContractedBlock.gif)
Code
1
BEGIN TRY
2
SELECT 1/0;
3
END TRY
4
BEGIN CATCH
5
SELECT
6
ERROR_NUMBER() AS ErrorNumber,
7
ERROR_SEVERITY() AS ErrorSeverity,
8
ERROR_STATE() AS ErrorState,
9
ERROR_PROCEDURE() AS ErrorProcedure,
10
ERROR_LINE() AS ErrorLine,
11
ERROR_MESSAGE() AS ErrorMessage;
12
END CATCH;
13![](/Images/OutliningIndicators/None.gif)
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步