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

 

转载于:https://www.cnblogs.com/xpp123/archive/2011/01/24/1942787.html