SQL 自定义函数获得树型结构下的所有子孙节点

create function ufGetChildCategoryIDs(@id int)
returns varchar(8000)
as
begin
     declare @t table(id int,parentid int,lev int)
     declare @lev int
     set @lev=1
     insert into @t select CategoryID,Parent, @lev from tblCategory where CategoryID=@id
     while(@@rowcount>0)
     begin
          set @lev=@lev+1
          insert into @t select a.CategoryID,Parent, @lev from tblCategory a,@t b
          where a.parent=b.id and b.lev=@lev-1
     end
     declare @cids varchar(8000)
     select @cids=isnull(@cids+',','')+ltrim(id) from @t order by lev
     return @cids
end
go