sql server 2000/2005递归

/*

递归查询 塗聚文
---SQL Server 2005环境下的实现：

*/
--生成测试数据   
create table Dept(ID int,ParentID int,msg varchar(20))   
insert into Dept select 1,0,'d'  
insert into Dept select 2,1,'s'  
insert into Dept select 3,1,'nl'  
insert into Dept select 4,2,'d'  
insert into Dept select 5,3,'s'  
insert into Dept select 6,5,'f'  
insert into Dept select 7,6,'d'  
go   

select * from Dept
Declare @Id Int    
Set @Id = 1;    ---在此修改父节点
 
With RootNodeCTE(Id,ParentID,msg)    
As    
(    
Select ID,ParentID,msg From Dept
Where ParentID In (@Id)    
Union All    
Select Dept.ID,Dept.ParentID,Dept.msg From RootNodeCTE    
Inner Join Dept   
On RootNodeCTE.Id = Dept.ParentID    
)      
Select * From RootNodeCTE  

---SQL Server 2000环境下的实现：
--生成测试数据  塗聚文 Geovin Du
create table Dept(ID int,ParentID int,msg varchar(20))  
insert into Dept select 1,0,'d' 
insert into Dept select 2,1,'s' 
insert into Dept select 3,1,'nl' 
insert into Dept select 4,2,'d' 
insert into Dept select 5,3,'s' 
insert into Dept select 6,5,'f' 
insert into Dept select 7,6,'d' 
go 

select * from Dept


--创建用户定义函数  
Create function [dbo].[GetChild](@ID varchar(10))  
returns @t table(ID varchar(10),ParentID varchar(10),msg varchar(20),Level int)  
as 
begin 
    declare @i int 
    set @i = 1  
    insert into @t select @ID,@ID,null,0 --当前级，本级，如果不要的话可以注释掉或再加个参数来选择操作  
    insert into @t select ID,ParentID,msg,@i from Dept where ParentID = @ID  
 
    while @@rowcount<>0  
    begin 
        set @i = @i + 1  
        insert into @t  
        select 
            a.ID,a.ParentID,a.msg,@i  
        from 
            Dept a,@t b  
        where 
            a.ParentID=b.ID and b.Level = @i-1  
    end 
    return 
end 
 
 
--执行查询  
select ID from dbo.GetChild(2)  
go  

--删除测试数据  
drop function GetChild  
drop table Dept