如何将SQL查询结果作为列表传入存储过程以删除WSUS更新

针对你的需求，我整理了几个实用方案，既能满足批量处理SELECT UpdateID FROM tbUpdate WHERE UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'结果的需求，也可以选择修改原存储过程使其支持批量输入：

方案一：修改存储过程支持表值参数（推荐） #

这是SQL Server中处理批量数据的标准方式，性能比循环更好，也更易维护，适合长期复用。

步骤1：创建表值类型 #

先定义一个用来传递多个UpdateID的表类型：

CREATE TYPE dbo.UpdateIDList AS TABLE (UpdateID UNIQUEIDENTIFIER);
GO

步骤2：修改原存储过程 #

更新spDeleteUpdateByUpdateID使其接受表值参数，同时保留原有的错误校验逻辑，新增批量处理的循环和错误记录：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spDeleteUpdateByUpdateID]
    @updateIDs dbo.UpdateIDList READONLY -- 表值参数，设置为只读
AS
SET NOCOUNT ON

-- 声明变量用于循环和错误处理
DECLARE @currentUpdateID UNIQUEIDENTIFIER,
        @localUpdateID INT,
        @retcode INT

-- 临时表记录失败的ID和错误信息，方便后续排查
CREATE TABLE #ErrorLog (
    UpdateID UNIQUEIDENTIFIER,
    ErrorMessage NVARCHAR(MAX),
    ErrorCode INT
)

-- 用游标遍历所有传入的UpdateID
DECLARE updateCursor CURSOR FAST_FORWARD FOR
SELECT UpdateID FROM @updateIDs

OPEN updateCursor
FETCH NEXT FROM updateCursor INTO @currentUpdateID

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        SET @localUpdateID = NULL
        SELECT @localUpdateID = LocalUpdateID FROM dbo.tbUpdate WHERE UpdateID = @currentUpdateID
        
        -- 校验UpdateID是否存在
        IF @localUpdateID IS NULL
        BEGIN
            RAISERROR('The update %s could not be found.', 16, 40, CAST(@currentUpdateID AS NVARCHAR(50)))
        END

        -- 校验是否被其他记录引用
        IF EXISTS (
            SELECT r.RevisionID 
            FROM dbo.tbRevision r 
            WHERE r.LocalUpdateID = @localUpdateID 
            AND (
                EXISTS (SELECT * FROM dbo.tbBundleDependency WHERE BundledRevisionID = r.RevisionID) 
                OR EXISTS (SELECT * FROM dbo.tbPrerequisiteDependency WHERE PrerequisiteRevisionID = r.RevisionID)
            )
        )
        BEGIN
            RAISERROR('The update %s cannot be deleted as it is still referenced by other update(s).', 16, 45, CAST(@currentUpdateID AS NVARCHAR(50)))
        END

        -- 执行删除逻辑
        EXEC @retcode = dbo.spDeleteUpdate @localUpdateID
        IF @@ERROR <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR('spDeleteUpdateByUpdateID got error from spDeleteUpdate for update %s', 16, -1, CAST(@currentUpdateID AS NVARCHAR(50)))
        END
    END TRY
    BEGIN CATCH
        -- 捕获错误并记录，避免单个ID失败中断整个批量任务
        INSERT INTO #ErrorLog (UpdateID, ErrorMessage, ErrorCode)
        VALUES (
            @currentUpdateID,
            ERROR_MESSAGE(),
            ERROR_NUMBER()
        )
    END CATCH

    FETCH NEXT FROM updateCursor INTO @currentUpdateID
END

CLOSE updateCursor
DEALLOCATE updateCursor

-- 如果有错误，输出错误日志并返回错误码
IF EXISTS (SELECT * FROM #ErrorLog)
BEGIN
    SELECT * FROM #ErrorLog
    RETURN 1
END

RETURN 0
GO

步骤3：调用修改后的存储过程 #

现在可以直接把你的查询结果作为输入，批量执行删除：

DECLARE @ids dbo.UpdateIDList
INSERT INTO @ids (UpdateID)
SELECT UpdateID FROM tbUpdate WHERE UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'

EXEC dbo.spDeleteUpdateByUpdateID @updateIDs = @ids

方案二：用游标直接批量执行原存储过程（无需修改原存储过程） #

如果只是临时批量处理，不想改动原有存储过程，可以用这个脚本逐个执行每个UpdateID：

SET NOCOUNT ON

-- 临时表记录失败的ID和错误信息
CREATE TABLE #ErrorLog (
    UpdateID UNIQUEIDENTIFIER,
    ErrorMessage NVARCHAR(MAX)
)

-- 游标遍历目标UpdateID
DECLARE updateCursor CURSOR FAST_FORWARD FOR
SELECT UpdateID FROM tbUpdate WHERE UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'

DECLARE @currentUpdateID UNIQUEIDENTIFIER
OPEN updateCursor
FETCH NEXT FROM updateCursor INTO @currentUpdateID

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        -- 执行原存储过程
        EXEC dbo.spDeleteUpdateByUpdateID @updateID = @currentUpdateID
    END TRY
    BEGIN CATCH
        -- 记录错误信息
        INSERT INTO #ErrorLog (UpdateID, ErrorMessage)
        VALUES (@currentUpdateID, ERROR_MESSAGE())
    END CATCH

    FETCH NEXT FROM updateCursor INTO @currentUpdateID
END

CLOSE updateCursor
DEALLOCATE updateCursor

-- 输出处理结果，区分成功和失败的ID
SELECT 'Success' AS Status, UpdateID FROM tbUpdate WHERE UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'
AND UpdateID NOT IN (SELECT UpdateID FROM #ErrorLog)
UNION ALL
SELECT 'Failed' AS Status, UpdateID FROM #ErrorLog
ORDER BY Status

方案三：使用WHILE循环处理（无游标方式） #

如果你不习惯用游标，也可以用临时表+WHILE循环的方式处理：

SET NOCOUNT ON

-- 创建临时表存储待处理的ID，并添加行号用于循环
SELECT UpdateID, ROW_NUMBER() OVER (ORDER BY UpdateID) AS RowNum
INTO #TempUpdateIDs
FROM tbUpdate WHERE UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'

DECLARE @totalRows INT = (SELECT COUNT(*) FROM #TempUpdateIDs),
        @currentRow INT = 1,
        @currentUpdateID UNIQUEIDENTIFIER

CREATE TABLE #ErrorLog (
    UpdateID UNIQUEIDENTIFIER,
    ErrorMessage NVARCHAR(MAX)
)

WHILE @currentRow <= @totalRows
BEGIN
    SELECT @currentUpdateID = UpdateID FROM #TempUpdateIDs WHERE RowNum = @currentRow

    BEGIN TRY
        EXEC dbo.spDeleteUpdateByUpdateID @updateID = @currentUpdateID
    END TRY
    BEGIN CATCH
        INSERT INTO #ErrorLog (UpdateID, ErrorMessage)
        VALUES (@currentUpdateID, ERROR_MESSAGE())
    END CATCH

    SET @currentRow = @currentRow + 1
END

-- 输出处理结果
SELECT 'Success' AS Status, UpdateID FROM #TempUpdateIDs
WHERE UpdateID NOT IN (SELECT UpdateID FROM #ErrorLog)
UNION ALL
SELECT 'Failed' AS Status, UpdateID FROM #ErrorLog
ORDER BY Status

-- 清理临时表
DROP TABLE #TempUpdateIDs
DROP TABLE #ErrorLog

注意事项 #

• 方案一的表值参数方式性能最优，适合频繁的批量处理场景；
• 方案二和三适合临时一次性处理，无需修改原有存储过程；
• 所有方案都加入了错误捕获机制，避免单个ID处理失败导致整个批量任务中断，同时可以清晰查看哪些ID处理失败。

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