递归算法实现的存储过程

CREATE PROC ProcName(@OrgId int)
AS
BEGIN
DECLARE @MenuTree TABLE(Id VARCHAR(32),Name NVARCHAR(32),ParentId VARCHAR(32),OrgId INT)
--向下递归，求子节点（机构）
DECLARE @MenuTreeDown TABLE(Id INT,Name NVARCHAR(32),ParentId INT,Level INT)
DECLARE @LevelDown INT
SET @LevelDown = 0
INSERT INTO @MenuTreeDown(Id,Name,ParentId,Level) SELECT Id,FvchrOrgName,FintParentId,@LevelDown FROM Sys_Organization WHERE Id = @OrgId

WHILE @@ROWCOUNT > 0
BEGIN
SET @LevelDown = @LevelDown + 1
INSERT INTO @MenuTreeDown(Id,Name,ParentId,Level) SELECT t1.Id,FvchrOrgName,FintParentId,@LevelDown FROM Sys_Organization t1
INNER JOIN @MenuTreeDown t2 ON t2.Id = t1.FintParentId WHERE t2.Level = @LevelDown - 1
END

--向上递归，求父节点（机构）
DECLARE @MenuTreeUp TABLE(Id INT,Name NVARCHAR(32),ParentId INT,Level INT)
DECLARE @LevelUp INT
SET @LevelUp = 10
INSERT INTO @MenuTreeUp(Id,Name,ParentId,Level) SELECT Id,FvchrOrgName,FintParentId,@LevelUp FROM Sys_Organization WHERE Id = @OrgId

WHILE @@ROWCOUNT > 0
BEGIN
SET @LevelUp = @LevelUp - 1
INSERT INTO @MenuTreeUp(Id,Name,ParentId,Level) SELECT t1.Id,FvchrOrgName,FintParentId,@LevelUp FROM Sys_Organization t1
INNER JOIN @MenuTreeUp t2 ON t2.ParentId = t1.Id WHERE t2.Level = @LevelUp + 1
END
--机构数据汇总
INSERT INTO @MenuTree(Id,Name,ParentId) SELECT Id,Name,ParentId FROM @MenuTreeDown UNION SELECT Id,Name,ParentId FROM @MenuTreeUp

SELECT * FROM @MenuTree
END

转载于:https://www.cnblogs.com/Gabriel-CC/p/7248953.html