Solved

I have a query which returns a column of ints, for each row, I want to call a stored procedure, how can this be done?

Posted on 2009-05-15
1
185 Views
Last Modified: 2012-05-07
I have a query which returns a column of ints, for each row, I want to call a stored procedure, how can this be done?

Let's say i have 'select my_key from my_table where cool=1'  and it returns:

2
3
6
8
9
12

from that, i would like to automatically run

EXEC my_sp @p1=2
EXEC my_sp @p1=3
EXEC my_sp @p1=6
EXEC my_sp @p1=8
EXEC my_sp @p1=9
EXEC my_sp @p1=12

Can I do something like

EXEC my_sp @p1=(select my_key from my_table where cool=1) or is more complicated than that?

Assume that my_key and @p1 are both type int
0
Comment
Question by:Matt_Du
1 Comment
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24400129
this should do:
declare @t table ( id int )
declare @id int 
insert into @t select my_key from my_table where cool=1
while @@rowcount > 0
begin
  select top 1 @id = id from @t
  if @@rowcount > 0
  begin
    exec my_sp @p1 = @id
  end
  delete @t where id = @id
  
end

Open in new window

0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question