得到已知结点下的所有字结点

ALTER     PROC [dbo].[sp_GetAllChildClass]
 @Class_id int
/*

========================================================
功能: 得到该类的所有子类
参数:
 @Class_id int  : 组（类）ID
备注:可以优化
========================================================

*/
AS

DECLARE @ids varchar(2000)
DECLARE @tmpids varchar(2000)
DECLARE @oldids varchar(2000)
DECLARE @sql varchar(2000)

--初始化变量
SET @ids =''
SET @tmpids=''
SET @oldids =''

--得到第一层子类
SELECT @ids = @ids + CONVERT(varchar,classid) + ',' FROM uds_class WHERE classparentid = @class_id  and classparentid <>classid
IF LEN(@ids)>0
 SET @ids = LEFT(@ids,LEN(@ids)-1)
--创建临时表
CREATE TABLE #class(id int)
--备份子类
SET @oldids = @ids

WHILE LEN(@oldids)>0
 BEGIN
  SET @sql = 'insert into #class select classid from uds_class where classparentid in (' + @oldids +')'
  EXEC (@sql)
  SET @tmpids=''
  SELECT @tmpids = @tmpids + CONVERT(varchar,id) + ',' FROM #class
  IF LEN(@tmpids)>0
   set @tmpids = LEFT(@tmpids,LEN(@tmpids)-1)

  SET @oldids = @tmpids
  DELETE FROM #class
  SET @ids = @ids + ',' + @tmpids
 END

IF LEN(@ids)>0
BEGIN
 SET @ids = LEFT(@ids,LEN(@ids)-1)  
 
 SET @sql ='select * from uds_class where classid in (' + @ids + ')'
 PRINT @sql
 EXEC (@sql)
END
ELSE
 SELECT *
  FROM uds_class
  WHERE 1=2