sqlserver2000 递归实现

create FUNCTION f_Cid(@ID bigint)
RETURNS @t_Level TABLE(ID bigint,Level int)
AS
BEGIN
 DECLARE @Level int
 SET @Level=1
 INSERT @t_Level SELECT @ID,@Level
 WHILE @@ROWCOUNT>0
 BEGIN
  SET @Level=@Level+1
  INSERT @t_Level SELECT a.areacode,@Level
  FROM DisLearing_AreaInfo a,@t_Level b
  WHERE a.parentcode=b.ID
   AND b.Level=@Level-1
 END
 RETURN
END
GO
-----------查询显示
SELECT a.*
FROM DisLearing_AreaInfo a,f_Cid(330624) b
WHERE a.areacode=b.ID
---------------建立存储过程
create proc getarealist
(
@areacode bigint
)
as
SELECT a.*
FROM DisLearing_AreaInfo a,f_Cid(@areacode ) b
WHERE a.areacode=b.ID
---------查询显示结果
exec getarealist 330624

查询结果