一种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 CODE=@CODE
-- 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