2

How can I execute the sp_1 for every ProductId and get a result set?

EXEC sp_1 (SELECT ID FROM Products)
Jude
  • 2,353
  • 10
  • 46
  • 70
  • http://stackoverflow.com/questions/4327955/ssis-result-set-fed-in-parallel-to-stored-procedure – Benny Jul 19 '14 at 09:47
  • 3
    Use a [cursor](http://stackoverflow.com/q/886293/11683) that would put each result in a temporary table, or [convert](http://stackoverflow.com/a/16419753/11683) your procedure to function. – GSerg Jul 19 '14 at 09:51

3 Answers3

2

Try this way. No direct query it seems. execute sp for each row

or try this , make small changes if needed.Use temp table to get values out of sp. Use the below inside a sp if needed.

begin
declare @ID int
declare @temp table (col1 int)
declare cur cursor for select distinct ID from products
open cur
fetch next from cur into @ID
truncate table @temp
while(@@FETCH_STATUS=0)
begin
insert into @temp (<'cols/output from procedure'>) exec (@ID)
end
select * from @temp
end
Recursive
  • 954
  • 7
  • 12
0

I would store the id's in a temp table and use a WHILE loop (AVOID CURSORS!)

DECLARE @prodid INT
SELECT prodid, 0 as Processed INTO #prod_ids FROM Products

WHILE EXISTS (SELECT prodid FROM #prod_ids WHERE Processed = 0)
BEGIN
     SELECT TOP 1 @prodid = prodid FROM #prod_ids WHERE Processed = 0
     EXEC sp_1(@prodid)
     UPDATE #prod_ids SET Processed = 1 WHERE prodid = @prodid
END
Hiram
  • 2,679
  • 1
  • 16
  • 15
  • Not everyone would agree with you on avoiding cursors http://www.sqlteam.com/article/cursors-an-overview – DeanOC Jul 20 '14 at 05:39
  • @DeanOC, I would definitely. If by any alternative; cursor can be avoided then it should be. – Rahul Jul 20 '14 at 05:42
  • 1
    @Rahul I try to avoid them myself, however they can be useful as long as you're aware of their limitations. This is one case where the speed difference in the loop construct may be inconsequential relative to executing a SP every cycle.' – DeanOC Jul 20 '14 at 05:50
  • @DeanOC, I am talking about in general and not specific to this question. About this question, can't really comment whether loop/cursor will be efficient cause I haven't done a benchmarking and without that if you say cursor will be better than loop then that just a speculation without any proof (at least to me). – Rahul Jul 20 '14 at 05:53
0

With dynamic query in SQL Server:

declare @cadena varchar(max) = ''

select @cadena = @cadena + 'exec sp_1 ' + ltrim(ID) + ';'
from Products;

exec(@cadena);
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dave Rincon
  • 308
  • 3
  • 10