BOM query

This one is worth the price of admission, if you've ever needed a BOM query

Here is my template query, it will work for BOM items nested no more than 6 deep, it uses nested cursors to accomplish the work.

Related Articles

... and you 'll find more on the SQL (Dynamics) Menu

IF exists (select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = 'dd_BOMComponents') begin
    DROP proc dd_BOMComponents
end
 
GO
  
CREATE proc dd_BOMComponents
--  dd_BOMComponents '100-2011'
 
@Item_Number varchar(31)
  
AS
 
set transaction isolation level read uncommitted
set nocount on
  
DECLARE @parent varchar(31)
DECLARE @c0 varchar(31)
DECLARE @c1 varchar(31)
DECLARE @c2 varchar(31)
DECLARE @c3 varchar(31)
DECLARE @c4 varchar(31)
DECLARE @c5 varchar(31),
    @quantity numeric(19,5), @uofm varchar(11), @makebuy varchar(2), @unitcost numeric(19,5), @mattcost numeric(19,5), @labcost numeric(19,5), @totcost numeric(19,5)
 
declare @o table (p varchar(31), c0 varchar(31),c1 varchar(31),c2 varchar(31),c3 varchar(31),c4 varchar(31),c5 varchar(31),
    quantity numeric(19,5), uofm varchar(11), makebuy varchar(1), unitcost numeric(19,5), mattcost numeric(19,5), labcost numeric(19,5), totcost numeric(19,5) )
 
--=================================================================
-- level 1
--=================================================================
DECLARE curName CURSOR LOCAL FAST_FORWARD FOR
    select PPN_I, CPN_I, QUANTITY_I, uofm, case isnull(e.MAKEBUYCODE_I,1) when 1 then 'M' when 2 then 'MB' else 'B' end as MakeBuy,
            i.STNDCOST
        from BM010115 b
            join iv00101 i on i.ITEMNMBR = b.PPN_I
            left join ivr10015 e on e.ITEMNMBR = i.ITEMNMBR
        where PPN_I = @Item_Number
   
OPEN curName
WHILE 1=1
BEGIN
    FETCH NEXT FROM curName INTO @parent, @c0, @quantity , @uofm, @makebuy, @unitcost
    if @@fetch_status <> 0 begin
        break
    end
   
    insert into @o (p      , c0, c1, c2  , c3 , c4 , c5 , quantity , uofm , makebuy , unitcost , mattcost , labcost , totcost)
        values     (@parent,@c0,null,null,null,null,null,@quantity , @uofm, @makebuy, @unitcost, @unitcost, @unitcost, @unitcost)
 
 
    --=================================================================
    -- level 2
    --=================================================================
    DECLARE cur0 CURSOR LOCAL FAST_FORWARD FOR
        select CPN_I, QUANTITY_I, uofm, case isnull(e.MAKEBUYCODE_I,1) when 1 then 'M' when 2 then 'MB' else 'B' end as MakeBuy,i.STNDCOST
            from BM010115 b
                join iv00101 i on i.ITEMNMBR = b.PPN_I
                left join ivr10015 e on e.ITEMNMBR = i.ITEMNMBR
            where PPN_I = @c0
   
    OPEN cur0
    WHILE 1=1
    BEGIN
        FETCH NEXT FROM cur0 INTO @c1, @quantity , @uofm, @makebuy, @unitcost
        if @@fetch_status <> 0 begin
            break
        end
   
        insert into @o (p      , c0, c1, c2 , c3 , c4 , c5 , quantity , uofm, makebuy, unitcost, mattcost, labcost, totcost)
            values     (@parent,@c0,@c1,null,null,null,null,@quantity , @uofm, @makebuy, @unitcost, @unitcost, @unitcost, @unitcost)
 
        --=================================================================
        -- level 3
        --=================================================================
        DECLARE CUR1 CURSOR LOCAL FAST_FORWARD FOR
            select CPN_I, QUANTITY_I, uofm, case isnull(e.MAKEBUYCODE_I,1) when 1 then 'M' when 2 then 'MB' else 'B' end as MakeBuy,i.STNDCOST
                from BM010115 b
                    join iv00101 i on i.ITEMNMBR = b.PPN_I
                    left join ivr10015 e on e.ITEMNMBR = i.ITEMNMBR
                where PPN_I = @c1
   
        OPEN CUR1
        WHILE 1=1
        BEGIN
            FETCH NEXT FROM CUR1 INTO @c2, @quantity , @uofm, @makebuy, @unitcost
            if @@fetch_status <> 0 begin
                break
            end
   
            insert into @o (p      , c0  , c1, c2, c3, c4  , c5, quantity, uofm, makebuy, unitcost, mattcost, labcost, totcost)
                values     (@parent,@c0  ,@c1,@C2,null,null,null,@quantity , @uofm, @makebuy, @unitcost, @unitcost, @unitcost, @unitcost)
   
 
            --=================================================================
            -- level 4
            --=================================================================
            DECLARE cur2 CURSOR LOCAL FAST_FORWARD FOR
                select CPN_I, QUANTITY_I, uofm, case isnull(e.MAKEBUYCODE_I,1) when 1 then 'M' when 2 then 'MB' else 'B' end as MakeBuy,i.STNDCOST
                    from BM010115 b
                        join iv00101 i on i.ITEMNMBR = b.PPN_I
                        left join ivr10015 e on e.ITEMNMBR = i.ITEMNMBR
                    where PPN_I = @c2
   
            OPEN cur2
            WHILE 1=1
            BEGIN
                FETCH NEXT FROM cur2 INTO @c3, @quantity , @uofm, @makebuy, @unitcost
                if @@fetch_status <> 0 begin
                    break
                end
   
                insert into @o (p      , c0, c1, c2, c3, c4 , c5 , quantity, uofm, makebuy, unitcost, mattcost, labcost, totcost)
                    values     (@parent,@c0,@c1,@C2,@c3,null,null,@quantity , @uofm, @makebuy, @unitcost, @unitcost, @unitcost, @unitcost)
   
                --=================================================================
                -- level 5
                --=================================================================
                DECLARE cur3 CURSOR LOCAL FAST_FORWARD FOR
                    select CPN_I, QUANTITY_I, uofm, case isnull(e.MAKEBUYCODE_I,1) when 1 then 'M' when 2 then 'MB' else 'B' end as MakeBuy,i.STNDCOST
                        from BM010115 b
                            join iv00101 i on i.ITEMNMBR = b.PPN_I
                            left join ivr10015 e on e.ITEMNMBR = i.ITEMNMBR
                        where PPN_I = @c3
   
                OPEN cur3
                WHILE 1=1
                BEGIN
                    FETCH NEXT FROM cur3 INTO @c4, @quantity , @uofm, @makebuy, @unitcost
                    if @@fetch_status <> 0 begin
                        break
                    end
   
                    insert into @o (p      , c0, c1, c2, c3, c4, c5 , quantity , uofm , makebuy , unitcost , mattcost , labcost  , totcost)
                        values     (@parent,@c0,@c1,@C2,@c3,@c4,null,@quantity , @uofm, @makebuy, @unitcost, @unitcost, @unitcost, @unitcost)
   
                    DECLARE cur4 CURSOR LOCAL FAST_FORWARD FOR
                        select CPN_I, @quantity , @uofm, @makebuy, @unitcost
                            from BM010115
                            where PPN_I = @c4
   
                    OPEN cur4
                    WHILE 1=1
                    BEGIN
                        FETCH NEXT FROM cur4 INTO @c5, @quantity , @uofm, @makebuy, @unitcost
                        if @@fetch_status <> 0 begin
                            break
                        end
   
                        insert into @o (p      , c0, c1, c2, c3, c4, c5, quantity , uofm , makebuy , unitcost, mattcost  , labcost  , totcost)
                            values     (@parent,@c0,@c1,@C2,@c3,@c4,@c5,@quantity , @uofm, @makebuy, @unitcost, @unitcost, @unitcost, @unitcost)
   
                    END
                    CLOSE cur4
                    DEALLOCATE cur4
 
 
 
                END
                CLOSE cur3
                DEALLOCATE cur3
 
 
 
            END
            CLOSE cur2
            DEALLOCATE cur2
        END
        CLOSE CUR1
        DEALLOCATE CUR1
 
   
    END
    CLOSE cur0
    DEALLOCATE cur0
   
END
CLOSE curName
DEALLOCATE curName
 
select o.p,
        ip.itemdesc as pItemDesc,
        o.c0,
        ic0.ITEMDESC as c0ItemDesc,
        o.c1,
        ic1.ITEMDESC as c1ItemDesc,
        o.c2,
        ic2.ITEMDESC as c2ItemDesc,
        o.c3,
        ic3.ITEMDESC as c3ItemDesc,
        o.c4,
        ic4.ITEMDESC as c4ItemDesc,
        o.c5,
        ic5.ITEMDESC as c5ItemDesc,
        o.quantity,
        o.uofm,
        o.makebuy,
        o.unitcost,
        o.mattcost,
        o.labcost,
        o.totcost
    from @o o
        join iv00101 ip on ip.itemnmbr = o.p
        left join iv00101 ic0 on ic0.itemnmbr = o.c0
        left join iv00101 ic1 on ic1.itemnmbr = o.c1
        left join iv00101 ic2 on ic2.itemnmbr = o.c2
        left join iv00101 ic3 on ic3.itemnmbr = o.c3
        left join iv00101 ic4 on ic4.itemnmbr = o.c4
        left join iv00101 ic5 on ic5.itemnmbr = o.c5
    order by 1,2,3,4,5
--  dd_BOMComponents '100-2011'
  
GO
  
  
GRANT EXEC ON dd_BOMComponents TO PUBLIC

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences