Thought of Genius

相信大家看過了 ｢ SQL Server 2008 HierarchyID ｣這篇文章，應該對於 HierarchyID 資料型態有些許的了解。但是當我們每次輸入資料時，都必須撰寫這麼複雜的T-SQL語法，實在很不方便。
所以使用 HierarchyID 通常都會搭配預存程序，這樣程式開發人員就不需要了解整個的架構，使用起來也比較方便。

以下沿用｢ SQL Server 2008 HierarchyID ｣的範例，介紹五個比較常用的預存程序。
<br />-- Create procedure to insert HierarchyEmp record<br />CREATE PROCEDURE usp_AddHierarchyEmp (@leaderid INT, @empid INT, @empname VARCHAR(15), @title VARCHAR(30)) AS<br />BEGIN<br />DECLARE @Parent HIERARCHYID, @LastChild HIERARCHYID<br />SELECT @Parent = OrgNode FROM HR.HierarchyEmp WHERE EmpID = @leaderid<br />SET TRANSACTION ISOLATION LEVEL SERIALIZABLE<br />BEGIN TRANSACTION<br />SELECT @LastChild = max(OrgNode)FROM HR.HierarchyEmp WHERE OrgNode.GetAncestor(1) = @Parent ;<br />INSERT INTO HR.HierarchyEmp VALUES(@Parent.GetDescendant(@LastChild, NULL), @empid, @empname, @title)<br />COMMIT<br />END ;<br />GO<br />-- Execute procedure to insert new child node<br />EXEC usp_AddHierarchyEmp 11, 101, 'Carlos Oliveira', 'Junior Technician'<br />EXEC usp_AddHierarchyEmp 13, 102, 'Rebecca Chambers', 'Junior Sales'<br />SELECT OrgNode.ToString() as OrgNodeText, * FROM HR.HierarchyEmp<br /><br /><br /><br />-- Create procedure to find out all Parents<br />CREATE PROCEDURE usp_FindParents (@empid INT) AS<br />BEGIN<br />DECLARE @BossNode HIERARCHYID<br />SELECT @BossNode = OrgNode FROM HR.HierarchyEmp WHERE EmpID = @empid ;<br />SELECT OrgNode.ToString() as OrgNodeText, * FROM HR.HierarchyEmp WHERE @BossNode.IsDescendantOf(OrgNode) = 1<br />END ;<br />GO<br />-- Execute procedure to find out all Parents<br />EXEC usp_FindParents 12<br /><br /><br /><br />-- Create procedure to find out all children<br />CREATE PROCEDURE usp_Findchildren (@empid INT) AS<br />BEGIN<br />DECLARE @BossNode HIERARCHYID<br />SELECT @BossNode = OrgNode FROM HR.HierarchyEmp WHERE EmpID = @empid ;<br />SELECT OrgNode.ToString() as OrgNodeText, * FROM HR.HierarchyEmp WHERE OrgNode.IsDescendantOf(@BossNode) = 1<br />END ;<br />GO<br />-- Execute procedure to find out all children<br />EXEC usp_Findchildren 2<br /><br /><br /><br />-- Create procedure to find out menber of level<br />CREATE PROCEDURE usp_FindForLevel (@empid INT) AS<br />BEGIN<br />SELECT OrgNode.ToString() as OrgNodeText, * FROM HR.HierarchyEmp WHERE OrgNode.GetLevel() = @empid<br />END ;<br />GO<br />-- Execute procedure to find out menber of level<br />EXEC usp_FindForLevel 3<br /><br /><br /><br />-- Create procedure to move OrgNode<br />CREATE PROCEDURE usp_MoveOrgHierarchyEmp(@oldMgr INT, @newMgr INT) AS<br />BEGIN<br />DECLARE @nold HierarchyID<br />DECLARE @nnew HierarchyID<br />SELECT @nold = OrgNode FROM HR.HierarchyEmp WHERE EmpID = @oldMgr ;<br />SET TRANSACTION ISOLATION LEVEL SERIALIZABLE<br />BEGIN TRANSACTION<br />SELECT @nnew = OrgNode FROM HR.HierarchyEmp WHERE EmpID = @newMgr ;<br />SELECT @nnew = @nnew.GetDescendant(max(OrgNode), NULL) FROM HR.HierarchyEmp WHERE OrgNode.GetAncestor(1)=@nnew ;<br />UPDATE HR.HierarchyEmp SET OrgNode = OrgNode.GetReparentedValue(@nold, @nnew) WHERE OrgNode.IsDescendantOf(@nold) = 1 ;<br />COMMIT TRANSACTION<br />END ;<br />GO<br />-- Save data as temporal table<br />SELECT * INTO ##tmp FROM HR.HierarchyEmp<br />-- Execute procedure to move OrgNode<br />EXEC usp_MoveOrgHierarchyEmp 11,13<br />SELECT t.OrgNode.ToString() as OrgNodeText, t.*, h.OrgNode.ToString() as OrgNodeText, h.*<br />FROM HR.HierarchyEmp as h FULL OUTER JOIN ##tmp as t ON t.OrgNode = h.OrgNode<br /><br /><br /><br />-- Clean up test environment<br />DROP PROCEDURE usp_AddHierarchyEmp, usp_MoveOrgHierarchyEmp;<br />DROP PROCEDURE usp_FindParents, usp_Findchildren, usp_FindForLevel;<br />