Improve company productivity with a Business Account.Sign Up

x
?
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
Medium Priority
?
214 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Watch the working video to know how to import Outlook PST/OST files to Amazon WorkMail. Kernel released this tool which is very easy to use and migrate single or multiple PST and OST files to Amazon WorkMail. To know more about Kernel Import PST to …

607 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