[SQL] HierarchyID - 函數

在 HierarchyID 三篇筆記內都有介紹、使用相關函數，該篇針對還有心得的函數來筆記

Parse

在 [SQL] HierarchyId - 字串路徑 筆記內是透過 CTE 來產生字串路徑，想說不知道 HierarchyID 是不是可以用字串來組成，結果當然是不行囉，另外在

• Parse 文件內備註提到，不能有尾端空白
• hierarchyid data type method reference 內提到節點不能有前置零

SELECT HierarchyID::Parse('/公司/總經理/人事/')
SELECT HierarchyID::Parse('/1 /')
SELECT HierarchyID::Parse('/1/1.01/')

上述都會拋出該 Exception，參數字串會是輸入字串，以 [ /公司/總經理/人事/] 為例說明

訊息 6522，層級 16，狀態 2，行 5 

執行使用者自訂常式或彙總 "hierarchyid" 時，發生 .NET Framework 錯誤:

Microsoft.SqlServer.Types.HierarchyIdException: 24001: SqlHierarchyId.Parse 失敗，因為輸入字串 '/公司/總經理/人事/' 不是 SqlHierarchyId 節點的有效字串表示。 Microsoft.SqlServer.Types.HierarchyIdException: 於 Microsoft.SqlServer.Types.SqlHierarchyId.Parse(SqlString input)

要避免節點發生異常，最保險方式就是使用 GetDescendant() 來產生

GetAncestor()

傳回該子節點的上階節點，n 代表往上 N 個節點，使用語法為

child.GetAncestor(n)

以 [SQL] HierarchyID - 批次 的 tblHID 為範例資料

取得上一階 (父節點，N = 1)

-- '/3/2/1/' 為 DepID = 7 (產線 1-1)
DECLARE @child HierarchyID = HierarchyID::Parse('/3/2/1/') 

-- '/3/2/' 為 DepID = 6 (工作站 1)
SELECT * , HID.ToString()
FROM tblHID
WHERE HID = @child.GetAncestor(1)

取得上兩階 (爺節點，N = 2)

-- '/3/2/1/' 為 DepID = 7 (產線 1-1)
DECLARE @child HierarchyID = HierarchyID::Parse('/3/2/1/') 

-- '/3/' 為 DepID = 4 (廠務 1)
SELECT * , HID.ToString()
FROM tblHID
WHERE HID = @child.GetAncestor(2)

取得上一階全部節點

GetAncestor(1) 為抓取父節點，但一開始理解成抓上一階全部節點，既然都誤會了就來寫看看囉

-- '/3/2/1/' 為 DepID = 7 (產線 1-1)
DECLARE @child HierarchyID = HierarchyID::Parse('/3/2/1/') 

SELECT * , HID.ToString()
FROM tblHID
WHERE HID.IsDescendantOf(@child.GetAncestor(2)) = 1
	AND HID.GetLevel() = @child.GetLevel() -1 

用該張圖來呈現上述三個範例結果