CREATE PROCEDURE dbo.GetDeptTree
(
@Code varchar(10)
)
AS
BEGIN
-- 1. 建立暫存表格(hold the parent/children-items)
CREATE TABLE #Relation
(
Code varchar(10), -- 部門代碼, 請改成用varchar, 不要使用char
Name nvarchar (200), -- 部門名稱
Dept_Level int , -- 部門層級(非必要)
parentCode varchar(10), -- 上層(父)部門代碼
Runs int -- 記錄是在第幾次執行時，存入暫存資料表
)
-- 2. 建立錨點(Anchor)
-- 將所輸入的部門存入暫存資料表
-- 因輸入的部門為本次展開的最上層的部門，因此其上層部門代碼設為NULL
-- Runs設為0，代表為初始化條件
INSERT INTO #Relation
( Code, Name, parentCode, Runs)
SELECT D.Code, D.Name, NULL, 0
FROM Dept D
WHERE 1=1
AND Code = @Code --'A000'
-- 3. 逐層展開(Loop as long as we find uninserted children)
WHILE @@ROWCOUNT > 0
-- 3.3. 重複執行，直至無任何資料被存入
BEGIN
-- Insert children that are not already in the temporary table
INSERT INTO #Relation
(Code, Name, parentCode, Runs)
SELECT T.Code, T.Name, T.parentCode, T.Runs +1
FROM #Relation R RIGHT JOIN -- 3.2. 3.1所取得的下屬部門，剔除已存在於暫存資料表的值
(
-- 3.1. 找出暫存資料表所有的下屬部門，當執行第二次時則會有重複值
SELECT T.Code, T.Name, T.parentCode, P.Runs
FROM Dept T, #Relation P
WHERE 1=1
AND P.Code = T.parentCode
) T
ON R.Code = T.Code
WHERE 1=1
AND R.Code IS NULL
END
-- 4. 傳回暫存資料表
SELECT P.Code, P.Dept_Level, P.parentCode, P.Name
FROM #Relation P
WHERE 1=1
ORDER BY P.Code, P.Dept_Level
-- 刪除暫存資料表 (非必要,但建議採行)
DROP TABLE #Relation
END
GO