一种BOM所用的Function...

---问题---

create table BOM(CODE varchar(4),C_CODE varchar(4),NUM int)

insert into BOM select 'A','B',2

insert into BOM select 'A','C',1

insert into BOM select 'C','D',2

insert into BOM select 'C','E',4

insert into BOM select 'E','F',3

insert into BOM select 'E','G',2

go

--动态参数化CODE的值

create function f_BOM(@CODE varchar(4),@NUM int)

returns @t table(CODE varchar(4),NUM int)

as

begin

    insert into @t(CODE,NUM) select C_CODE,NUM*@NUM FROM BOM WHERE [email protected]

-- C,1

    while @@rowcount<>0

    begin

        insert into @t(CODE,NUM)

        select

            a.C_CODE,a.NUM*b.NUM

        from

            BOM a,@t b

        where

            a.CODE=b.CODE

            and

            not exists(select 1 from @t where CODE=a.C_CODE)

    end

--    Code=C

    return

end

go

select * from dbo.f_BOM('C',1)

--A                   --1

  |--B                      --2                     

  |--C                      --1             

     |--D              --2                     

     |--E             --4

        |--F              --3

        |--G          --2