查找递归表结构的父节点下所有子节点

表结构Catalog
ID  Name ParentID

创建临时表
#tempNode
ID Level

存储过程:
Declare @subCount int
Declare

Declare  @subCount  int
Declare  @Level
while  @subCount  > 0
begin
  insert   into  # temp (ID, Level )
  Select  ID,@Level + 1   from  Catalog
  where  ParentID  in
 (
   select  ID  from  # temp   where   Level   =  @Level
 )
set  @Level  =  @Level + 1
Select  @subCount  =   Count ( * )  from  Catalog
where  ParentID  in  
(
  select  ID  from  # temp   where   Level   =  @Level
)

end