sql-server - 中止违反外键约束的存储过程

Question

如果由于违反外键约束而无法成功执行删除语句，是否有办法中止 SQL Server 中的存储过程？默认情况下，该过程似乎会忽略错误并继续执行下一条语句。

然而，对于其他类型的错误(例如，从不存在的表中删除)，过程将中止。

示例程序:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE  [dbo].[TestSP]
AS
BEGIN
    SET NOCOUNT ON;

    print 'BEFORE';

    DELETE FROM ExistingWithConstraints;
    print 'AFTER DELETE ExistingWithConstraints';

    DELETE FROM NonExisting;
    print 'AFTER DELETE NonExisting';
END

产生输出(注意上面的最后一条消息没有打印出来):

BEFORE
<snip constraint violation error message>
AFTER DELETE ExistingWithConstraints    
<snip invalid object name error message>

Answer 1

使用事务和正确的错误处理

CREATE PROCEDURE  [dbo].[TestSP]
AS
SET XACT_ABORT, NOCOUNT ON
DECLARE @starttrancount int

BEGIN TRY
    SELECT @starttrancount = @@TRANCOUNT

    IF @starttrancount = 0
        BEGIN TRANSACTION

    DELETE FROM ExistingWithConstraints;
    DELETE FROM NonExisting;

    IF @starttrancount = 0 
        COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION
    RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
END CATCH
GO

这是基于我在这里的回答:Nested stored procedures containing TRY CATCH ROLLBACK pattern?