call stored procedure for each value in a table

I’m fairly new to this group. My company has put me in charge of datawarehousing for our Automatic Call distribution data so that we can report on it later with Crystal Reports.

Currently we do not control our own ACD system for the helpdesk and thus we are generating reports on a 15 minute interval basis. There are 4 reports which run each 15 minute interval and parsing them is easy using a Vbscript and importing them into a database.

However, I’d like to move away from the vbscript approach and use TSQL coupled with job scheduling with Microsoft SQL 2000 to do the same thing.

I’m fairly new to SQL and have the basic essentials needed to query and update and insert data, and my company has scheduled more training for me, however my classes are not offered until mid July and I’d like to get a jump start on this.

My situation is this:

I’ve got a list of filenames in a directory stored in a table on the SQL server. It updates itself with a stored procedure I wrote and it works fine. The table consists only of a list of filenames.

I’d like to execute a stored procedure for each item in this list using the filename as a parameter. It sounds like a simple task but I’m racking my brain here trying to figure it out.

Does anyone have a solution to this problem?

Thanks,

Look in your Books Online documentation for "cursors, fetching". There's even an example on that page to show you how to go through a result set, one row at a time. However, cursors should be used only as a last resort, since they are rather inefficient. Is it possible for you to write another stored procedure that joins to the filename table and does its thing?