SQL中使用IN子句删除多行时遇外键错误仍继续执行的方法

你的核心问题在于单条DELETE语句是原子性操作——只要IN列表里有一条ID因外键约束报错，整个语句会回滚所有已执行的删除，哪怕SMO设置了ContinueOnError也没用，因为SQL Server会把这条语句当作一个完整事务处理。

下面是三种可行的解决方案：

1. 逐个删除+错误捕获（最稳妥，适合需要保留错误日志的场景） #

把批量拆成单个删除操作，用TRY/CATCH捕获每个ID的删除错误，跳过有问题的记录：

-- 先把要删除的ID存入临时表
DECLARE @TargetIds TABLE (Id INT PRIMARY KEY)
INSERT INTO @TargetIds VALUES (1), (2), (3), (4);

DECLARE @CurrentId INT;
DECLARE IdCursor CURSOR FOR SELECT Id FROM @TargetIds;

OPEN IdCursor;
FETCH NEXT FROM IdCursor INTO @CurrentId;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        DELETE FROM CUSTOMERS WHERE ID = @CurrentId;
        PRINT '成功删除ID: ' + CAST(@CurrentId AS VARCHAR(10));
    END TRY
    BEGIN CATCH
        PRINT '删除ID ' + CAST(@CurrentId AS VARCHAR(10)) + ' 失败: ' + ERROR_MESSAGE();
    END CATCH
    FETCH NEXT FROM IdCursor INTO @CurrentId;
END

CLOSE IdCursor;
DEALLOCATE IdCursor;

2. 先筛选可删除的ID再批量删除（效率更高，适合大数量场景） #

先查询出没有外键引用的ID，再批量删除这些安全的记录，避免逐个操作的性能损耗：

-- 假设外键关联表是ORDERS，关联字段为CustomerId
DECLARE @TargetIds TABLE (Id INT PRIMARY KEY)
INSERT INTO @TargetIds VALUES (1), (2), (3), (4);

-- 只删除无外键引用的ID
DELETE FROM CUSTOMERS
WHERE ID IN (
    SELECT t.Id
    FROM @TargetIds t
    LEFT JOIN ORDERS o ON t.Id = o.CustomerId
    WHERE o.CustomerId IS NULL
);

-- 可选：查看未删除的ID
SELECT Id AS 未删除ID FROM @TargetIds
WHERE Id NOT IN (SELECT ID FROM CUSTOMERS WHERE ID IN (SELECT Id FROM @TargetIds));

3. 拆分DELETE为独立批处理配合SMO ContinueOnError #

如果一定要用SMO的ContinueOnError，需要把每个ID的删除拆成单独的SQL语句（每个语句是一个批处理），这样SMO会逐个执行，跳过报错的批处理：

DELETE FROM CUSTOMERS WHERE ID = 1;
DELETE FROM CUSTOMERS WHERE ID = 2;
DELETE FROM CUSTOMERS WHERE ID = 3;
DELETE FROM CUSTOMERS WHERE ID = 4;

在SMO代码中执行这个多批处理脚本时，设置ContinueOnError = true，这样某一条语句报错不会影响后续语句执行。

内容的提问来源于stack exchange，提问作者Mohan R