请教一个关于产品子结构的SQL语句

CREATE TABLE [dbo].[boms] (

    piID varchar(20),  --父ID

    picID varchar(20)  --子ID

)

GO



insert into boms(piID,picID) values('001','0011')

insert into boms(piID,picID) values('001','0012')

insert into boms(piID,picID) values('0012','0013')

insert into boms(piID,picID) values('0012','0014')

insert into boms(piID,picID) values('0013','00133')

insert into boms(piID,picID) values('0013','00134')

insert into boms(piID,picID) values('00133','001331')

insert into boms(piID,picID) values('00133','001332')

go



--通过以下函数可以实现查询下级的编码：

create function f_getP(@piID varchar(20)) 

returns @re table(picID varchar(20),lev int,piID varchar(20),TFlag varchar(10)) 

as

begin

    declare @l int 

    set @l=0 

    insert @re select picID,@l,piID,'本级ID' from [boms] where piID = @piID

    while @@rowcount>0 

    begin 

        set @l=@l+1

        insert @re select a.picID,@l,a.piID,'下级ID'

        from [boms] a,@re b

        where a.piID=b.picID and b.lev=@l-1 and a.picID is not null

    end 

    update @re set lev=@l-lev

    return

end

go



declare @piID varchar(10)

set @piID = '001'



select * from dbo.f_getP(@piID)