Below is a stored procedure that will find all BOM parents where the given item is used. The item filter will search for all parts with a wildcard.
create procedure sp_IV_BOMWhereUsed
(
@itemFilter nvarchar(30)
)
as
set nocount off;
– define globals
declare @dataAreaId nvarchar(5); set @dataAreaid = ‘dat’
– define temp table for gathering parents
declare @temp table
(
bomId nvarchar(30),
itemId nvarchar(30),
bomlevel int,
seen bit,
rowgroup nvarchar(100),
recType int,
recId int identity
)
– declare variables to track state
declare @recId int; set @recId = 0;
declare @itemid nvarchar(30);
declare @bomlevel int; set @bomlevel = 0
declare @pass int; set @pass = 0;
– add first level items to check
insert into @temp
select “, itemId, 1, 0, “, 1
from inventTable
where
dataAreaId = @dataAreaId and
itemId like replace(@itemFilter, ‘’, ‘%’)
– set first level row group string
update @temp set rowGroup = cast(power(100,bomLevel) + recId as nvarchar(100)) where rowGroup=”
– loop while records
while exists(select top 1 recId from @temp where seen = 0)
begin
– get the next record to process
set @itemId = null
select top 1
@itemId = itemId,
@bomLevel = bomLevel,
@recId = recId
from @temp
where seen = 0
– check if no more records
if @itemId is null break
– add the next level of records
insert into @temp
select distinct bv.bomId, bv.itemId, @bomLevel + 1, 0, “, 2
from bomVersion bv inner join bom b on bv.dataAreaId = b.dataAreaid and bv.bomId = b.bomId
where
bv.dataAreaId = @dataAreaId and
b.itemId = @itemId and
not exists( select recId from @temp where bomId = bv.bomId and itemId = bv.itemId)
– set the row group for these new records
update @temp set
rowGroup = (select top 1 rowGroup from @temp where recid=@recid) + cast(100 + recId as nvarchar(100))
where
rowGroup=”
– increate the pass
set @pass = @pass + 1
– remember this record was seen
update @temp set seen = 1 where recid = @recId
end
– return the records
select t., i.itemName,
replicate(‘.’, 4 * (t.bomlevel-1)) as indent
from
@temp t
inner join
inventTable i
on i.dataAreaId = @dataAreaid and
t.itemId = i.itemId
order by
rowgroup