国内最全IT社区平台 联系我们 | 收藏本站
华晨云阿里云优惠2
您当前位置:首页 > 数据库 > Sqlserver > SQL SERVER 函数递归取树状结构

SQL SERVER 函数递归取树状结构

来源:程序员人生   发布时间:2013-12-16 18:02:40 阅读次数:2592次
表结构:
USE [Test]
GO
/****** 对象: Table [dbo].[PersonTree] 脚本日期: 09/25/2009 00:17:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PersonTree](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[ParentID] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

SQL函数:

CREATE FUNCTION dbo.getPerson(@personID INT)
RETURNS @table TABLE(id INT, pname VARCHAR(50))
AS
BEGIN
--INSERT INTO @table SELECT id, pname FROM PersonTree WHERE parentid = @personID
DECLARE @id INT
DECLARE @pname VARCHAR(50)
DECLARE myCursor CURSOR FOR
SELECT id, pname FROM PersonTree WHERE parentid = @personID
OPEN myCursor
FETCH NEXT FROM myCursor INTO @id, @pname;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @table VALUES(@id,@pname);
INSERT INTO @table SELECT * FROM dbo.getPerson(@id);
FETCH NEXT FROM myCursor INTO @id, @pname;
END

CLOSE myCursor
DEALLOCATE myCursor

RETURN
END
生活不易,码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
程序员人生
------分隔线----------------------------
分享到:
------分隔线----------------------------
关闭
程序员人生