3

I just started SQL with Microsoft SQL Server 2008 R2 and I want to select a list of Ids and run each of them through a stored procedure but am not sure how to do it.

SELECT Id 
FROM UserId 
WHERE ProgramId = @ProgramId

Then, I have created a procedure called temp_sp_UpdateIds

Normally I can just run the stored procedure with 

EXEC temp_sp_UpdateIds @ProgramId

but I am not sure how to run the stored procedure with the list of Ids returned from the select statement and place it under @ProgramId

Do I need to store the Ids in a local table or something?

Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
overloading
  • 1,210
  • 4
  • 25
  • 46

4 Answers4

6

You can use Table-Valued Parameters

Creating a Table Type and SP

CREATE TYPE dbo.ListOfIds AS TABLE(Id int PRIMARY KEY)
GO

CREATE PROCEDURE dbo.temp_sp_UpdateIds
 (
  @ListOfIds dbo.ListOfIds READONLY
  )
AS
BEGIN
...body of procedure
END
GO

Calling a Procedure with a Table-Valued Parameter

DECLARE @ListOfIds dbo.ListOfIds
INSERT @ListOfIds
SELECT Id
FROM UserId
WHERE ProgramId = @ProgramId

EXEC dbo.temp_sp_UpdateIds @ListOfIds

See SQLFiddle

Aleksandr Fedorenko
  • 16,594
  • 6
  • 37
  • 44
  • thx! I am trying to do this but how do I use ``@ListOfIds`` inside my sp? When I ref to the variable it returns ``Must declare the scalar variable @ListOfIds``? And for the table-valued paramters/table type, is it simply creating a temporary table which only in the scope of the script? thx in advance. – overloading May 07 '13 at 22:13
  • I suggested using Table-valued parameters to send multiple rows of data to procedure, without creating a temporary table.Inside procedure you can only read data from a Table-valued parameter (e.g. perform whether operations a restrict or filtering data, etc.) In some cases better performance than temporary tables or other ways to pass a list of parameters. In answer was added a simple demo. – Aleksandr Fedorenko May 08 '13 at 10:36
2

You're unfortunately looking a cursor. Concept is you will provide your query in the first block, declare the variable(s) you'll need to operate your proc and then iterate through them.

DECLARE CSR CURSOR
READ_ONLY
FOR SELECT ProgramId FROM UserId 

DECLARE @programid int
OPEN CSR

FETCH NEXT FROM CSR INTO @programid
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        EXECUTE temp_sp_UpdateIds @programId
    END
    FETCH NEXT FROM CSR INTO @programId
END

CLOSE CSR

DEALLOCATE CSR
GO

The other option that comes to mind is to generate the EXEC calls in SQL, concatenate all of that together with a semicolon and then exec that.

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • Do you mean @programId in the first fetch? – John Dewey May 07 '13 at 20:53
  • @JohnDewey I wasn't quite sure how the OP used it. They are executing a SELECT based on the value of programid in the query but then using that same programid in the EXEC call. I figured they better understood their logic and just needed the basic parts of how to call a proc for each row in a resultset. I'm open to improving the clarity of the answer if you have suggestions – billinkc May 07 '13 at 20:57
0

If you can change the proc to a function (that returns a value - even just null) you can simply do this:

SELECT temp_sp_UpdateIds(id) FROM UserId WHERE ProgramId=@ProgramId
Bohemian
  • 412,405
  • 93
  • 575
  • 722
-1

In normal case you may do something like this:

SELECT * FROM UserId WHERE ProgramId in (SELECT ProgramId FROM t WHERE ...)

If procedure fill @ParamId by somthing like select result with one column - it must work

Eugene
  • 1,899
  • 1
  • 13
  • 10