Run sp with select-result as input

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Run sp with select-result as input

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-14 : 13:04:20

I need to run a stored procedure with the values from a select as input. For various reasons I cannot place this select in the procedure itself so I need to do it with maybe some dynamic sql or something. Pseudo-code:

FOR EACH Field1, Field2, Field3 IN (SELECT TOP 3 Field1, Field2 Field3 FROM table)
  EXEC myProc Field1, Field2, Field3

How would I do something like this...?

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-14 : 22:11:00

Can you put the results of the sproc into a table first, like you are showing below? If the table has an identity column on it, you can loop through the values with a while loop, running your second stored proc based on whatever values are in that iteration of the loop.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-15 : 06:25:39

Yes, I have thought about putting the values into a table-variable and this will work just fine. Would it be like this? DDL:

DECLARE @myTable table 
  (ID int IDENTITY(1, 1), Field1 int, Field2 int, Field3 int)
DECLARE @Counter int, @Field1 int, @Field2 int, @Field3 int

INSERT INTO @myTable
SELECT 1, 2, 3 UNION ALL SELECT 3, 4, 5 UNION ALL
SELECT 6, 7, 8 UNION ALL SELECT 9, 10, 11

SET @Counter = 1
WHILE (@Counter <= (SELECT MAX(ID) FROM @myTable))
  BEGIN
    SELECT @Field1 = Field1, @Field2 = Field2, @Field3 = Field3
    FROM @myTable
    WHERE ID = @Counter

    EXEC myProc @Field1, @Field2, @Field3
    SET @Counter = @Counter + 1
  END

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-15 : 08:37:47

The logic in this script here looks just about the same as the logic in a cursor to me, but as it is not a cursor this would be alot more efficient, right? I could easily convert most if not all of my cursors to this (yes I have a few of them left), and if I did that could I start worrying a little less about the performance then?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-15 : 20:45:25

I use this logic all the time instead of cursors. It is much more processor-friendly than cursors. Several years ago, I went through tons of code in my library and converted all of it to while loops instead of cursors. It was a lot of work, but I only had to do it once. Now I copy and paste a lot. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-15 : 20:54:34

Cool! I have never thaught about doing it like this but to me this would be just the same as using a cursor (just about the same amount of code and the same logic). I would have to do that someday also

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-15 : 20:58:26

One tiny improvement. Make another variable for your max - @max.

Set that to the max value and use that for the top end of your while loop. Will save you a lot of reads you don't need at all.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-15 : 21:05:48

Yup, I just thaught of that right after posting but thanx anyway :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources