[SOLVED] How to use results from an SQL Query as a list to delete (WSUS) updates

How to use results from an SQL Query as a list to delete (WSUS) updates

My problem is that I want to use the results from a SELECT query as the input values for a Stored Procedure. The issue is that the SP will only accept Scalar values, and I do not know SQL and so have been struggling to find a workaround or solution.

I want to modify the following Proc to accept multiple values to be used within the query:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[spDeleteUpdateByUpdateID]
    @updateID UNIQUEIDENTIFIER
AS
SET NOCOUNT ON
DECLARE @localUpdateID INT
SET @localUpdateID = NULL
SELECT @localUpdateID = LocalUpdateID FROM dbo.tbUpdate WHERE UpdateID = @updateID
IF @localUpdateID IS NULL
BEGIN
    RAISERROR('The update could not be found.', 16, 40)
    RETURN(1)
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 cannot be deleted as it is still referenced by other update(s).', 16, 45)
    RETURN(1)
END
DECLARE @retcode INT
EXEC @retcode = dbo.spDeleteUpdate @localUpdateID
IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
    RAISERROR('spDeleteUpdateByUpdateID got error from spDeleteUpdate', 16, -1)
    RETURN(1)
END
RETURN (0)

TLDR: if anyone knows a quick way for me to use the results from SELECT UpdateID FROM tbUpdate WHERE UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629' to run exec spDeleteUpdateByUpdateID @updateID= i'd be extremely grateful.

Solution

There are some examples online of people using cursors to clean up WSUS. It will be slow but you are presumably only running it once. As mentioned there are other strategies for WSUS cleanup that should probably be investigated first.

DECLARE @var1 INT
DECLARE @msg nvarchar(100)

-- Get obsolete updates into temporary table
-- insert your own ID's here if you wish
CREATE TABLE #results (Col1 INT)
INSERT INTO #results(Col1) EXEC spGetObsoleteUpdatesToCleanup

DECLARE WC Cursor

FOR SELECT Col1 FROM #results

OPEN WC
    FETCH NEXT FROM WC INTO @var1
    WHILE (@@FETCH_STATUS > -1)
    BEGIN 
        SET @msg = 'Deleting' + CONVERT(varchar(10), @var1)
        RAISERROR(@msg,0,1) WITH NOWAIT EXEC spDeleteUpdateByUpdateId @var1
        FETCH NEXT FROM WC INTO @var1 
    END

CLOSE WC

DEALLOCATE WC
DROP TABLE #results