sql-server - 如何在内部事务中抛出异常而不结束 SQL Server 存储过程执行?
问题描述
我的目标是向调用者抛出异常,但继续执行SQL Server
存储过程。所以,从本质上讲,我想要完成的是一个try..catch..finally block
,即使 SQL Server 没有 try..catch..finally 块的概念,据我所知。
我有一个示例stored procedure
来说明。这只是我想出的一个例子,所以请不要过多关注表模式。希望您了解我在这里尝试执行的操作的要点。无论如何,存储过程包含explicit transaction
一个exception
在catch block
. 在 try..catch 块之后还有进一步的执行,但它永远不会执行,如果THROW
执行的话。据我了解,至少在 SQL Server 中,THROW 无法区分内部事务和外部事务或嵌套事务。
在这个存储过程中,我有两个表:Tbl1和Tbl2。Tbl1primary key
在Tbl1.ID上有一个。Tbl2foreign key
在EmpFK上有一个映射到Tbl1.ID。EmpID有一个唯一的约束。不能将重复记录插入到Tbl1中。Tbl1和Tbl2在ID上都有主键,并使用标识增量进行自动插入。存储过程有三个输入参数,其中之一是employeeID。
在内部事务中,在Tbl1中插入了一条记录——添加了一个新的员工 ID。如果它失败了,这个想法是事务应该优雅地出错,但存储的过程应该仍然继续运行直到完成。无论表插入成功还是失败,EmpID都会在后面被用来填充EmpFk。
在 try..catch 块之后,我通过传递到存储过程的employeeID参数执行Tbl1.ID的查找。然后,我在 TBl2 中插入一条记录;Tbl1.ID是 Tbl2.EmpFK 的值。
(您可能会问“为什么要使用这样的模式?为什么不将这么小的数据集合并到一个表中?”同样,这只是一个示例。它不一定是员工。你可以选择任何东西。它只是一个小部件。想象一下Tbl1可能包含一个非常非常大的数据集。一成不变的是有两个表具有主键/外键关系。)
这是示例数据集:
Tbl1
ID EmpID
1 AAA123
2 AAB123
3 AAC123
Tbl2
ID Role Location EmpFK
1 Junior NW 1
2 Senior NW 2
3 Manager NE 2
4 Sr Manager SE 3
5 Director SW 3
这是示例存储过程:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_TestProc]
@employeeID VARCHAR(10)
,@role VARCHAR(50)
,@location VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @employeeFK INT;
BEGIN TRY
BEGIN TRANSACTION MYTRAN;
INSERT [Tbl1] (
[EmpID]
)
VALUES (
@employeeID
);
COMMIT TRANSACTION MYTRAN;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION MYTRAN;
END;
THROW; -- Raises exception, exiting stored procedure
END CATCH;
SELECT
@employeeFK = [ID]
FROM
[Tbl1]
WHERE
[EmpID] = @employeeID;
INSERT [Tbl2] (
[Role]
,[Location]
,[EmpFK]
)
VALUES (
@role
,@location
,@employeeFK
);
END;
所以,再次,我仍然想将错误返回给调用者,即记录错误,但我不希望它停止在其轨道上冷执行存储过程。它应该继续非常类似于 try..catch..finally 块。这可以通过THROW
或我必须使用替代方法来完成吗?
也许我弄错了,但它不是THROW
升级版,RAISERROR
而且,我们应该使用前者来处理异常吗?
我过去曾RAISERROR
在这些情况下使用过,它非常适合我。但是THROW
,imo 是一个更简单、更优雅的解决方案,并且可能是更好的实践。我不太确定。
提前谢谢你的帮助。
解决方案
固定不变的是有两个表具有主键/外键关系。
在内部事务中使用 THROW 不是做你想做的事的方法。从您的代码来看,您想要插入一个新员工,除非该员工已经存在,然后,无论该员工是否已经存在,您都希望在第二次插入子表中使用该员工的 PK/id。
一种方法是拆分逻辑。这是我的意思的伪代码:
IF NOT EXISTS(Select employee with @employeeId)
INSERT the new employee
SELECT @employeeFK like you are doing.
INSERT into Table2 like you are doing.
如果在传递已经存在的@employeeId 时仍然需要引发错误,则可以在 IF 之后放置一个 ELSE,并填充一个字符串变量,并在 proc 结束时,如果填充了变量,则 throw/引发错误。
推荐阅读
- r - How to make a frequency table from a data frame in R
- highcharts - 多个 yAxis 时,Highcharts xAxis 有额外的间隙
- c# - AspNet.Core Identity - 无法登录用户
- android - Goodle Ad Manager SDK 与 AdMob SDK
- java - Java,如何在比较器中按字母顺序对字符进行排序
- c - 我正在尝试制作自定义 pow() 函数,但我的 for 循环不起作用 - C
- php - 使用数据库主机的最佳方式是什么?
- javascript - 输入 URL 并扫描网页以查找特定单词(请求规划建议)
- powershell - 如何将新数据插入现有文件?
- google-chrome-extension - 使用 chrome.windows.create() 在 PWA 模式下启动一个窗口