CREATE function fBOMmat (@matcode varchar (16)) returns @bom table (Num smallint, MatCode varchar (16), Coef real, String varchar (1000), advannum int) asbeginif exists (select * from T142 where matcodem = @ matcode) begindeclare @matcoded varchar (16), @ coef real, @ num smallint, @ advannum intselect @ num = 1declare bom cursor forselect matcoded, coef, isnull (a.advannum, 1) from T142 b left join T052 a on b.matcoded = a.matcode and a.isvalid = 1 where matcodem = @ matcodeopen bomfetch next from bom into @ matcoded, @ coef, @ advannumwhile @@ fetch_status = 0begininsert @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 = 1Fetch Next from Bom Into @ Matcoded, @ Coef, @ AdvanNumend Close Bomdeallocate Bom End Returnend
