PROCEDURE GetDeptTree

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