--Check the Qty of Item I
create table bom
(
Pitemid varchar(20),
Itemid varchar(20),
Qty real
)
insert into bom
select 'A','B',2 union all
select 'A','C',3 union all
select 'B','D',3 union all
select 'B','E',1 union all
select 'D','H',2 union all
select 'D','L',10 union all
select 'H','I',5 union all
select 'C','F',5 union all
select 'C','G',1
go
create function fn_test(@id varchar(20))
returns int
as
begin
declare @return int
select @id=Pitemid,@return=isnull(@return,1)*Qty from bom where itemid=@id
while @@rowcount!=0
select @id=Pitemid,@return=isnull(@return,1)*Qty from bom where itemid=@id
return (@return)
end
go
select Itemid,dbo.fn_test(Itemid) as Qty
from bom
order by itemid
go
drop table bom
drop function fn_test
Itemid Qty
-------------------- -----------
B 2
C 3
D 6
E 2
F 15
G 3
H 12
I 60
L 60