Insert multiple values into stored procedure

Question

I have 2 System Stored Procedures.

1) exec spGetObsoleteUpdatesToCleanup

2) exec spDeleteUpdate @localUpdateID=000000

The first proc retrieves a column called "localUpdateID" with 30 000 rows. In the second proc you have specify the retrieved row from the first proc at "@localUpdateID=000000"

Copying each one of the 30 000 rows and pasting it into the second proc will take forever.

I have tried:

1) exec spDeleteUpdate @localUpdateID = exec spGetObsoleteUpdatesToCleanup

2) exec spDeleteUpdate @localUpdateID IN ('00000', '00001')

Answer 1

You need to update your first stored procedure so it returns data using an OUT parameter.

You can create a temporary table variable that you will use to temporarily store the output IDs of your first stored procedure and then send them to the second.

It should end up looking something like this:

declare @OutputIdList table(localUpdateId int)
exec spGetObsoleteUpdatesToCleanup @OutputIdList=@OutputIDs OUTPUT
exec spDeleteUpdate select localUpdateId  from @OutputIdList

Microsoft has a blog post that explains how to give OUT parameters to stored procedures.

Here's some copy-pasted data from it.

    CREATE PROCEDURE Myproc
    @parm varchar(10),
    @parm1OUT varchar(30) OUTPUT,
    @parm2OUT varchar(30) OUTPUT
    AS
      SELECT @parm1OUT='parm 1' + @parm
     SELECT @parm2OUT='parm 2' + @parm
GO
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @parmIN VARCHAR(10)
DECLARE @parmRET1 VARCHAR(30)
DECLARE @parmRET2 VARCHAR(30)
SET @parmIN=' returned'
SET @SQLString=N'EXEC Myproc @parm,
                             @parm1OUT OUTPUT, @parm2OUT OUTPUT'
SET @ParmDefinition=N'@parm varchar(10),
                      @parm1OUT varchar(30) OUTPUT,
                      @parm2OUT varchar(30) OUTPUT'

EXECUTE sp_executesql
    @SQLString,
    @ParmDefinition,
    @parm=@parmIN,
    @parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT

SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"
go
drop procedure Myproc

Answer 2

If this is a quick one-off type thing, I'd suggest putting the results of the first procedure into a temp table or table variable (simulated by my insert statement, but I've included comments for the actual code) and then just creating dynamic SQL statements that you can simply copy and paste back into a query window.

Declare @t table (LocalId int)
insert into @t (LocalId) values(1),(2),(3)

--insert into @t(LocalId)
--exec spGetObsoleteUpdatesToCleanup

select 'exec spDeleteUpdate @localUpdateID=' + convert(varchar(10),LocalId) as Text from @t

---

| Text                                 |
|--------------------------------------|
| exec spDeleteUpdate @localUpdateID=1 |
| exec spDeleteUpdate @localUpdateID=2 |
| exec spDeleteUpdate @localUpdateID=3 |

Answer 3

Why don't you just take the body of the first procedure, and build a big set of statements to execute?

Assume the first procedure looks like this:

CREATE PROCEDURE dbo.spGetObsoleteUpdatesToCleanup
AS
    SELECT LocalUpdateID FROM dbo.SomeTable WHERE ...;

Then rip out that SELECT statement and change it to:

DECLARE @sql nvarchar(MAX) = N'';
SELECT @sql += N'EXEC dbo.spDeleteUpdate @localUpdateID='
  + RTRIM(LocalUpdateID) + N';' + CHAR(13) + CHAR(10)
  FROM dbo.SomeTable WHERE ...;
PRINT @sql; -- this won't show all 30,000 rows
-- EXEC sys.sp_executesql @sql;

---

Better yet, rip out the bodies of BOTH procedures. The first one is some kind of SELECT, like the above, and the second is a simple delete, like:

DELETE dbo.SomeTable WHERE LocalUpdateID = @LocalUpdateID;

This can be much more practical to do using a single DELETE:

DELETE t 
  FROM dbo.SomeTable AS t
  INNER JOIN dbo.SomeOtherTable AS o
  ON t.LocalUpdateID = o.LocalUpdateID
  WHERE ...whatever criteria marks these as obsolete...;

Just keep in mind that logging can be an issue and you might consider breaking this up into smaller batches.

---

If you show us the bodies of the two procedures, we can give you much more direct advice. If all we know is that the two procedures are black boxes, we can't provide anything extremely specific.