计算BOM

CREATE function fBOMmat(@matcode varchar(16)) returns @bom table(Num smallint,MatCode varchar(16),Coef real,String varchar(1000),advannum int) as begin if exists(select * from T142 where matcodem=@matcode) begin declare @matcoded varchar(16),@coef real,@num smallint,@advannum int select @num=1 declare bom cursor for select matcoded,coef,isnull(a.advannum,1) from T142 b left join T052 a on b.matcoded=a.matcode and a.isvalid=1 where matcodem=@matcode open bom fetch next from bom into @matcoded,@coef,@advannum while @@fetch_status=0 begin insert @bom values(@num,@matcoded,@coef,@coef,@advannum) if exists(select * from T142 where matcodem=@matcoded) insert @bom select num+1,matcode,coef*@coef,cast(@coef as varchar)+'*'+string,advannum+@advannum from fBOMmat(@matcoded) select @num=1 fetch next from bom into @matcoded,@coef,@advannum end close bom deallocate bom end return end