根据父级编号获取无限树结构数据表中所有子节点的数据 -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]