How can I execute the sp_1 for every ProductId and get a result set?
EXEC sp_1 (SELECT ID FROM Products)
-
stackoverflow.com/questions/4327955/…Benny– Benny2014-07-19 09:47:21 +00:00Commented Jul 19, 2014 at 9:47
-
3Use a cursor that would put each result in a temporary table, or convert your procedure to function.GSerg– GSerg2014-07-19 09:51:03 +00:00Commented Jul 19, 2014 at 9:51
Add a comment
|
3 Answers
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
Comments
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
4 Comments
DeanOC
Not everyone would agree with you on avoiding cursors sqlteam.com/article/cursors-an-overview
Rahul
@DeanOC, I would definitely. If by any alternative; cursor can be avoided then it should be.
DeanOC
@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.'
Rahul
@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).
With dynamic query in SQL Server:
declare @cadena varchar(max) = ''
select @cadena = @cadena + 'exec sp_1 ' + ltrim(ID) + ';'
from Products;
exec(@cadena);
