Bank5


I have a table that contains a CourseID field and a Status field.

CourseID Status
1 NULL
2 NULL
3 NULL

I need to loop through this table and run a stored procedure on each CourseID to fill in the status field. How can I do this I'm not a big fan of cursors and hardly ever use them, but for this case, I'm not sure if there's a way around it. I'm using SQL Server 2005 btw.






Re: How to step through a table, run a stored procedure on each record and update the table

Kent Waldrop Jl07


If possible, I would suggest converting the stored procedure into a function and joining the function to the table using a CROSS APPLY. Should look something like this:

using a Scalar function:

Code Snippet

update targetTable

set status = dbo.newFunction (CourseID)

Using a table function:

Code Snippet

update targetTable

set status = b.status

from targetTable a

cross apply newFunction (courseId) b






Re: How to step through a table, run a stored procedure on each record and update the table

Bank5

That would be ideal, but my stored procedure creates temporary tables so I can't put it in a function.





Re: How to step through a table, run a stored procedure on each record and update the table

Kent Waldrop Jl07

Can your temp tables be replaced with table variables



Re: How to step through a table, run a stored procedure on each record and update the table

Bank5

Awesome, thanks for the help! Obviously this way is much more efficient than a cursor too.

When is it better to use a function than a stored procedure




Re: How to step through a table, run a stored procedure on each record and update the table