Recursive search

In MSSQL 2005 there is a recursive search facility. The clause used is [with]. Example is below.

with PartSearch (Part_number,Replacement_part, level) as {

select Part_number,Replacement_part, 0 as level from wfm_parts p

union all

select p.Part_number, p.Replacement_part, ph.level+1 from wfm_parts p

inner join PartSearch ph on ph.part_number = p.Replacement_part }

select Part_number,Replacement_part, level from PartSearch where level > 0

Obviously, you have to use stored_procedures to do it.

PartSearch is a pseudo name created. It can be any name. The above is not tested as I do not have a good database sample to run the example. The syntax should be correct.