[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

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?

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
Matt_Du
Asked:
Matt_Du
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now