根据父级编号获取无限树结构数据表中所有子节点的数据

根据父级编号获取无限树结构数据表中所有子节点的数据 -sql server 2005
-------------------------create FUNCTION  script-----------------------------
CREATE FUNCTION f_tDictRegion_GetChilds(@ParentID INT)
RETURNS @Rt TABLE(ID INT,Level INT,Name VARCHAR(8000))
AS
BEGIN
    DECLARE @L INT
    SET @L=0
    INSERT @Rt SELECT ID,@L,Name
    FROM tDictRegion where ParentID=@ParentID
    WHILE @@ROWCOUNT>0
    BEGIN
        SET @L=@L+1
        INSERT @Rt SELECT a.ID,@L,b.Name+'/'+a.Name
        FROM tDictRegion a,@Rt b
        WHERE a.ParentID=b.ID and b.Level=@L-1
    END
    RETURN
END
-------------------------create TABLEscript-----------------------------
USE [test]
GO
/****** 对象:  Table [dbo].[tDictRegion]    脚本日期: 04/06/2012 12:39:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tDictRegion](
 [ID] [int] NOT NULL,
 [NAME] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
 [LEAVE] [int] NOT NULL,
 [ParentID] [int] NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

---------DATA---------
111 111 0 0
222 222 1 111
333 333 2 222
444 444 3 333
555 555 5 111
666 666 6 1
777 777 7 0
---------SELECT---------
SELECT [ID]
      ,[NAME]
      ,[LEAVE]
  FROM [test].[dbo].[tDictRegion]