SQL SERVER 函数递归取树状结构

概述表结构： 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_P… 表结构：
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 总结

以上是内存溢出为你收集整理的SQL SERVER 函数递归取树状结构全部内容，希望文章能够帮你解决SQL SERVER 函数递归取树状结构所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。