Board index » SQL Server » Executing SP for all results

SQL Server286
I would like to have my stored procedure executed for each item returned by

a Select query





Ex.



Declare @file_id varchar(5)



Select @file_id = file_id from GEN where this_value <>''



execute sp_mystored_procedure @file_id



go



Currently it only works for one record



How do I get it to do it for all returned records?



The top select statement may return 1000 records, but only one pass is made

through the stored procedure



Hints Please



Thanks



Darrell

darrellp@btmcpa.com


-

Doc,



Try this ......



CREATE TABLE #TEMPIT (@file_id int, FLAG int)

INSERT INTO #TEMPIT

SELECT file_id , 0

FROM GEN

WHERE this_value <>''



SET @file_id = (SELECT TOP 1 file_id from GEN where FLAG = 0)

WHILE NOT (@file_id IS NULL)

BEGIN

execute sp_mystored_procedure @file_id

UPDATE #TEMPIT SET FLAG = 1 WHERE file_id=@file_id

SET @file_id = (SELECT TOP 1 file_id from GEN where FLAG = 0)

END



Enjoy,



Tony



"Doc Parker" wrote:

Doc,



Try this ......



CREATE TABLE #TEMPIT (@file_id int, FLAG int)

INSERT INTO #TEMPIT

SELECT file_id , 0

FROM GEN

WHERE this_value <>''



SET @file_id = (SELECT TOP 1 file_id from GEN where FLAG = 0)

WHILE NOT (@file_id IS NULL)

BEGIN

execute sp_mystored_procedure @file_id

UPDATE #TEMPIT SET FLAG = 1 WHERE file_id=@file_id

SET @file_id = (SELECT TOP 1 file_id from GEN where FLAG = 0)

END



Enjoy,



Tony







"Tony Scott" wrote: