sql server programming: I've a Bill of Material table that looks some what like this:

Table BOM:
Part# Component#
1 A
1 B
1 C
A X
A Y
B M
B N
B O
B P
B Q
B R
R S
R T
X Z

Column 1 is the part number and Column 2 is the component number.

As you can see, part number 1, is made out of A, B, and C.

But then again part A is made out of X and Y.

And B is made out of M, N, O, P, Q, R.

R is made out of S and T.

X is made out of Z.

That means C, M, N, O, P, Q, S, Y and Z are purchase parts. They are
bought from the suppliers directly. Others are made out of these
purchase parts.

I have another table that holds the cost for all the purchase parts and
all the manufactured parts. How do I write a stored procedure so that
if I supply the part number, it gets me the total cost for that part?
There can be one component part OR several. There is no limit on how
many components you can have on a part. If I want to user Cursor and
While loop, how do I loop thru each part to get the cost, add up and
keep going until I get the total cost for the parent part? Any input
will be highly appreciated.

Thanks in advance..

Hello, VJ

If you are using SQL 2005, read the following article by Itzik Ben-Gan,
especially the part about recursive CTE-s (see Figure 8 in the
article):
http://msdn.microsoft.com/msdnmag/issues/04/02/TSQLinYukon/

Razvan

Hi Razvan -

Thanks for the input. Unfortunately, we are not SQL 2005 yet. Though, I
was able to figure out the solution using Cursor, While loop and the
temp tables. It is working fine - though after researching a little bit
- I came across some Extended Stored Procs such as sp_OACreate etc.
that allows to create an object and then properties can be assigned to
this object. I am hoping to get this done using these extended stored
procs if possible. Anyone, with experience with these procs, please let
me know. Not sure, how this impacts the performance either. I will have
to do a little research to figure this out - I can post the code, if
anyone is interested.

Thanks..


[quoted text, click to view]