SQL 条件提交 - 即使没有错误，也要检查结果

Question

我有一个用于取消取消加载的简短查询。我希望能够只更新@LoadID并让它运行，并在成功时在最后提交，如果不成功则回滚。

我已经在事务中获得了更新，并且可以围绕它添加try-catch，但这并不总是能捕获所有内容。

基本上我必须更新一个表并从另一个表中删除一行。然后我想检查以确保更新正确发生并且该行已删除。然后回滚或提交。

这是我目前拥有的：

DECLARE @LoadID varchar(10) = 'LF2025391'
SELECT * FROM dbo.SAR_AppointmentMaster AS SAR
WHERE SAR.LoadID = @LoadID
SELECT * FROM dbo.SAR_AppointmentTimes AS SAR
WHERE SAR.AppointmentID IN(SELECT SAR.AppointmentID FROM dbo.SAR_AppointmentMaster AS SAR
WHERE SAR.LoadID = @LoadID)
BEGIN TRY
--/*
BEGIN TRAN  --commit    rollback
UPDATE dbo.SAR_AppointmentMaster
SET dbo.SAR_AppointmentMaster.RecordStatus = 0
WHERE dbo.SAR_AppointmentMaster.LoadID = @LoadID
DELETE FROM dbo.SAR_AppointmentTimes
FROM dbo.SAR_AppointmentTimes AS SAR2
WHERE SAR2.AppointmentID IN(SELECT SAR.AppointmentID FROM dbo.SAR_AppointmentMaster AS SAR
WHERE SAR.LoadID = @LoadID)
AND SAR2.DateType = 'CANCEL'
--*/
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
SELECT * FROM dbo.SAR_AppointmentMaster AS SAR
WHERE SAR.LoadID = @LoadID
SELECT * FROM dbo.SAR_AppointmentTimes AS SAR
WHERE SAR.AppointmentID IN(SELECT SAR.AppointmentID FROM dbo.SAR_AppointmentMaster AS SAR
WHERE SAR.LoadID = @LoadID)

更新或删除可能会失败，并且不会被try-catch块捕获。这就是为什么我想使用类似If语句的东西来在提交或发布错误和回滚之前进行更新和删除检查。但是我没有找到任何可以在 T-SQLselect之外工作的东西。

Answer 1

从您在评论中提到的内容来看，似乎您只需要在不符合您的某些期望时在try中抛出一个异常，例如在下面的模式中：

declare @exes table (x varchar(3));
insert @exes values ('xe'), ('exe'), ('xx'), ('x');
begin tran
begin try
update @exes set x = 'x' where x <> 'ex';
if @@rowcount = 0 throw 50000, 'Hey, no rows were updated', 1;
delete @exes where x = 'ex';
if @@rowcount = 0 throw 50000, 'Hey, no rows were deleted', 1;
commit;
end try
begin catch
rollback;
throw;
end catch
select      *
from        @exes