Would like help designing a query

I am trying to write a query that will return all the packaging materials that we need to have in stock on a given date.  Here is some background:

I can write a query that will return all the part numbers and quantities that are scheduled to be produced by a given date.

I can write a query that will call a function provided by our ERP supplier that will return all the packaging materials, and quantities for any given item.

The only way that I would know to provide a list of the total packaging requirements needed on a given date would be to run the first query and insert the items into a temp table, then create a loop that would run through each item in the temp table and call the function that returns the packaging material requirements for each item.

Ive got to believe that there is a better way to do this, can someone steer me in the right direction ??

Thanks !!
Who is Participating?
SS2005 has the APPLY clause which is designed exactly for that purpose.  Short of that or reverse engineering the vendor's function, I'm afraid you stuck with a procedural solution.  However, I don't think you need a temp table for the results of the first query.  Just iterate thru the results of the first query using a cursor. You probably want to collect the results of the second query in a temp table inside the loop of your cursor.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.