SQL Server 2000/2005 recursion

/*

Recursive query for Paint poly
---implementation in a SQL Server 2005 environment:

*/
--Generate test data
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; ---Modify the parent node here

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

---implementation in a SQL Server 2000 environment:
--Generate test data 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 a user-defined function
Create function [dbo]. [Getchild] (@ID varchar (10))
Returns @t table (ID varchar), ParentID varchar (ten), msg varchar (+), level int)
As
Begin
DECLARE @i int
Set @i = 1
Insert INTO @t Select @ID, @ID, null,0-current level, this level, if not, you can comment out or add a parameter to select the operation
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


--Execute Query
Select ID from dbo. Getchild (2)
Go

--Delete test data
Drop function Getchild
drop table Dept

SQL Server 2000/2005 recursion