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