SQL SERVER 函数递归取树状结构

发布时间：2020-12-12 12:36:23 所属栏目：MsSql教程来源：网络整理

导读：表结构： 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,

表结构：
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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!