如何在不结束SQL Server存储过程执行的情况下在内部事务中抛出异常

Question

我的目标是向调用方抛出异常，但继续执行SQL Server存储过程。因此，从本质上讲，我试图实现的是try..catch..finally block，尽管SQL Server没有尝试的概念。。接住最后封锁，据我所知。

我有一个示例stored procedure要说明。这只是我突然想到的一个例子，所以请不要太关注表模式。希望你能理解我在这里要做的事情的要点。无论如何，存储的proc包含一个explicit transaction，该CCD_4在catch block中抛出exception。审判结束后还有更多的处决。。catch块，但如果执行了THROW，则它从未执行过。据我所知，至少在SQL Server中，THROW无法区分内部事务和外部事务或嵌套事务。

在这个存储过程中，我有两个表：Tbl1和Tbl2Tb1在Tbl1上有一个primary key。ID。Tb1在映射到Tbl1EmpFK的EmpID有一个唯一约束。不能将重复记录插入Tbl1。Tbl1和Tbl2都在ID上具有主键，并使用身份增量进行自动插入。存储的proc有三个输入参数，其中一个是employeeID。

在内部事务中，在Tbl1中插入一条记录——添加一个新的员工ID。如果它失败了，我们的想法是事务应该正常地出错，但存储的proc仍然应该继续运行，直到完成。无论表插入成功还是失败，稍后都将使用EmpID来填写EmpFk。

尝试之后。。catch块，我通过传递到存储过程中的employeeID参数来执行Tbl1.ID的查找。然后，我在TBl2中插入一条记录Tb1.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;

因此，我仍然希望将错误返回给调用方，即记录错误，但我不希望它停止存储过程的冷执行。它应该像尝试一样继续下去。。接住最后阻止。这可以用THROW完成吗？或者我必须使用其他方法？

也许我错了，但THROW不是RAISERROR的升级版吗？今后，我们应该使用前者来处理异常？

我过去在这些情况下使用过RAISERROR，它非常适合我。但THROW是一个更简单、更优雅的解决方案，imo，可能是未来更好的实践。我不太确定。

提前感谢您的帮助。

Answer 1

最重要的是有两个表有一个主键/外键关系。

在内部事务中使用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的末尾，如果填充了变量，则抛出/引发错误。