-1

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.

RyT
  • 13
  • 4
  • is there a reason this can't be a single query that you input only UpdateTypeId into? – Aero9999 May 19 '22 at 15:16
  • A procedure expects a literal or variable as its parameters. Assign the output of your subquery to a variable, and then pass that to your procedure. – Thom A May 19 '22 at 15:16
  • Because of by above comment, @Aero9999 . – Thom A May 19 '22 at 15:16
  • UpdateTypeId is a single GUID which could be passed into a proc – Aero9999 May 19 '22 at 15:17
  • @Aero9999, expressions other then a const or a var are not allowed in the context. `exec myproc @p =2` is ok `exec myproc @p =(1+1)` fails. – Serg May 19 '22 at 15:25
  • So i'll give it a go assigning the output to a variable - can't do this until Monday now but i'll return with my results then. Thanks @Larnu for the response and explanation – RyT May 20 '22 at 09:18
  • Not sure i'm with you @Larnu, EXEC myProc 'B551F2C8-8380-491B-A51F-436E51CDD08F' is totally valid? – Aero9999 May 20 '22 at 12:47
  • It is, but `exec spDeleteUpdateByUpdateID @updateID=(SELECT UpdateID FROM tbUpdate WHERE UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629')` is not, @Aero9999 . – Thom A May 20 '22 at 12:52
  • My idea was that he should put that select inside the proc itself and only pass in the GUID, spDeleteUpdateByUpdateTYPEId. – Aero9999 May 20 '22 at 12:57
  • @Aero9999 there is no sp for DeleteUpdateByUpdateTypeID unfortunately. That would be far too easy! – RyT May 23 '22 at 13:57
  • @Larnu I've had a look at assigning the output of the SELECT query to a variable, however in the content guides i've been looking at, i have seen mention that it will only call the last value associated to the variable. I'm looking for something that would recurse through the output of the select command and run spDeleteUpdateByUpdateID against each individual entry of the select output. Is what i've been seeing false and should i try with a variable anyways, or would i be wasting my time? – RyT May 23 '22 at 14:00
  • You want `DECLARE @updateID uniqueidentifier = ({Your SubQuery}); EXEC dbo.spDeleteUpdateByUpdateID @updateID` @Ryt . – Thom A May 23 '22 at 14:03
  • @Larnu you're assuming he is only deleting one entry at a time? – Aero9999 May 23 '22 at 21:51
  • @Ryt Although it's not my preferred approach you can 'loop' through the results of the select. https://stackoverflow.com/questions/886293/how-do-i-execute-a-stored-procedure-once-for-each-row-returned-by-query that should give an example – Aero9999 May 23 '22 at 21:52
  • The OP hasn't stated that `@updateID` is a table type parameter, @Aero9999 , as I presume so, yes. – Thom A May 23 '22 at 22:11
  • So i backed up my db and ran the query as @Larnu suggested and received the following result: `Msg 512, Level 16, State 1, Line 3 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. Msg 50000, Level 16, State 40, Procedure spDeleteUpdateByUpdateID, Line 11 [Batch Start Line 2] The update could not be found.` I have seen reference to creating a table from the output of the subquery so perhaps that is the way i need to go? At this point i just want it done and dusted! – RyT May 24 '22 at 08:39
  • So *is* `@updateID` a Table Type parameter, @RyT ? If not, and it's scalar value, what are you expecting to happen when you try to assign *multiple* values to it. As the name suggests, a scalar variable can only contain a *scalar* value. – Thom A May 24 '22 at 08:49
  • I did state in my original post, before you edited it, that i'm new to SQL, so bear with me as i don't know 90% of the jargon you have been using and i've been looking it up just so i can answer, @Larnu. To answer your question, `@updateID` does state it is scalar value. Please let me reiterate how new i am to this by saying i have less than 2 working days experience with SQL so literally all of this is new to me. – RyT May 24 '22 at 09:37
  • A *scalar* parameter **cannot** accept multiple values; they are scalar values. If you want to send multiple values for your parameter `@updateID` then it needs to be a table type parameter. Alternatively, you would need to change it to accept the parameters for `UpdateTypeID`, and then it would get the necessary `UpdateID` values from `tbUpdate`. Without the definition of `spDeleteUpdateByUpdateID`, however, it's impossible for us to advise how you would change your procedure accordingly. – Thom A May 24 '22 at 09:45
  • I assumed it was implied that the subquery is returning multiple updateID values as it makes the most sense with regards to what he is doing [all updates of a type]. @RyT do you have permissions on this DB to create your own Stored Procedure? if so we can help you create one where the input is a singl UpdateTypeId. Otherwise i think you will need to use 'cursors' to loop through all your updateId's and call the existing SP one at a time. Let me know the direction you want and i'll try to assist – Aero9999 May 24 '22 at 09:49
  • *"I assumed it was implied that the subquery is returning multiple updateID values "* When you are assigning a value from a subquery to a *scalar* value quite the opposite; it implies the query returns a scalar value. – Thom A May 24 '22 at 09:50
  • Can i not just create a loop to run the stored procedure against a list of values? I've just stumbled across cursors, which provisionally look like they might work. `spDeleteUpdateByUpdateID` is an sp on our Windows Updates Server, which i am trying to utilise to delete the bothersome driver entries from the database, of which there are over 130k. Does that help? @Larnu – RyT May 24 '22 at 09:55
  • A loop would be awfully slow, @RyT . You *could* but I strongly recommend a set based method. If you need to loop 130,000 times, that will likely (literally) take hours as opposed to seconds. – Thom A May 24 '22 at 09:56
  • @Aero9999 Funny, i just mentioned cursors! Yes, i have full access permissions to the server so would be happy to create a stored procedure for this. – RyT May 24 '22 at 09:57
  • @Larnu No loop, got it! – RyT May 24 '22 at 09:57
  • Huh, surely WSUS has better strategies for cleanup? https://learn.microsoft.com/en-us/mem/configmgr/sum/deploy-use/software-updates-maintenance – Aero9999 May 24 '22 at 11:27
  • @Aero9999 WSUS inbuilt cleanup is atrocious, I've had to create and modify a bunch of powershell scripts to decline and remove updates on a regular basis. I had done the same to remove updates from the database, but it was taking around 2 minutes per update to remove it from the database. It worked well, until it stopped working, and it was too slow. I'm going to look at creating a stored proc as i'd like to get something that could be run again in the future without a huge time overhead. – RyT May 24 '22 at 12:09
  • Alas, without deeper knowledge in SQL i am lost. I created an SProc from the `spDeleteUpdateByUpdateID` Sproc but it looks to be full of variables! I think it is possible to modify it to accept TABLE values, however it's beyond my knowledge: `@updateID UNIQUEIDENTIFIER AS SET NOCOUNT ON DECLARE @localUpdateID INT SET @localUpdateID = NULL SELECT @localUpdateID = LocalUpdateID FROM dbo.tbUpdate WHERE UpdateID = @updateID` There are references throughout the Sproc to the @localUpdateID variable, any thoughts on this guys? @Larnu @Aero9999 – RyT May 24 '22 at 13:48
  • As i mentioned, without the definition of the procedure, I can't advise on how you would amend it. – Thom A May 24 '22 at 13:49
  • @Larnu I'm pretty sure i have defined the procedure quite well by this point - what is it that you believe is missing please and i'll do my best to give you this 'definition'? – RyT May 26 '22 at 09:14
  • You haven't given the definition of the procedure at all, @RyT , only the `EXEC` statement. What the SQL within the procedure is, I have no idea. – Thom A May 26 '22 at 09:20
  • @Larnu Well then in that case, can you tell me where to find it and i'll get you an answer? I can't re-iterate enough how little i know about SQL. If it helps, I'm using MSSQL? – RyT May 26 '22 at 14:16
  • You need to get the definition from your database, @RyT . In SSMS that would be by right clicking the procedure in the object explorer, and then selecting Modify or Script Procedure As and selecting the Create To or Alter To option. – Thom A May 26 '22 at 14:18
  • I've already done this with 2 separate Stored Procs and i can't see any "Definition" for either of them anywhere, @Larnu . I can see the contents of the Stored Procs, i.e. the queries it contains - but no "definition". Could you give an example of what you mean so i know what im looking for? Many thanks – RyT May 30 '22 at 07:58
  • *" the queries it contains "* That *is* the **definition**, @RyT ... – Thom A May 30 '22 at 07:59
  • Oh, well now there is another issue - the proc is 500 characters too long to post as a comment so i try to edit my OP, @Larnu – RyT May 30 '22 at 08:05
  • Then take that definition and turn it into a [mre]... We are, however, 31 comments in now though so I'm done at this stage I'm afraid. – Thom A May 30 '22 at 08:06
  • I mean, the past 16 comments have just been me not knowing what you and Aero were talking about but i can't expect you to have the same motivation as me for solving the problem. I was really hoping to create a Stored Proc with your help and share that as an answer, but i'll take the route of cursors. Thanks for all the help – RyT May 30 '22 at 09:25

1 Answers1

0

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
Aero9999
  • 127
  • 5
  • Im going to mark this as the answer as i now think the only way to achieve what i need to achieve would be a WHILE loop, or a Cursor! – RyT Jun 15 '22 at 15:09