Executing a Stored Procedure in a loop

Hi There!

I need to execute a stored procedure for each row returned from a Select statement for now the only way for me to do it is by creating a cursor and loop thought the result and passing the parameters for the stored procedure and call it in the loop.

My question is. Is there any way I can put the 'execute [spStoredProcedure] param1, param2' with in the select statement so I won't need to create a cursor and manually call the stored procedure for each record?

Hi Lazer,

If you're using 2000, have you had a look at functions? EG:

select fn_MyFunction(a.Column1)

from MyTable

where something = something

Or, if using 2005, have you had a look at CROSS/OUTER APPLY?

select *

from MyTable a t

cross apply fn_MyFunction(t.Column1) as b

The difference being that cross/outer apply must be a table valued function...

Cheers

Rob

|||

Hi Rob,

i using 2000, i want to show a record set for user with the data only have monday date. even the date in database is not monday but i would like to take the early monday date. so can i have a sample how this loop function work?

select fn_MyFunction(a.dtanswerdate)

from answer

where something = something

if i want to update or change the date into monday date for display.

regards

terence chua