Sql [hierarchyid]類型如何動態插入層級數據

[hierarchyid] 是個不錯的數據類型，提供了很方便並且高性能的樹型查詢，網上找了很多資料都沒有講到如何做到動態插入這個關鍵點，從MSDN認真看了下資料寫出了一個DEMO

CREATE TABLE  EmployeeOrg
(
   OrgNode hierarchyid PRIMARY KEY CLUSTERED,
   OrgLevel AS OrgNode.GetLevel(),
   EmployeeID int UNIQUE NOT NULL,
   EmpName varchar(20) NOT NULL,
   Title varchar(20) NULL
) ;
go

CREATE PROC AddEmp(@mgrid int, @empid int, @e_name varchar(20), @title varchar(20)) 
AS 
BEGIN
   DECLARE @mOrgNode hierarchyid, @lc hierarchyid
   SELECT @mOrgNode = OrgNode 
   FROM  EmployeeOrg 
   WHERE EmployeeID = @mgrid
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
   BEGIN TRANSACTION
      SELECT @lc = max(OrgNode) 
      FROM  EmployeeOrg 
      WHERE OrgNode.GetAncestor(1) =@mOrgNode ;

      INSERT  EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
      VALUES(@mOrgNode.GetDescendant(@lc, NULL), @empid, @e_name, @title)
   COMMIT
END ;

go

--插入根
INSERT  EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES (hierarchyid::GetRoot(), 1, '藍燈', 'Marketing Manager') 
go
--隨機數
declare @p1 int
select @p1=convert(int, 100000000*rand())
declare @p2 int
select @p2=convert(int, 100000000*rand())

--插入軟體部門子集
EXEC AddEmp 1, @p1, '研發部門老大', 'Marketing Specialist' ;
EXEC AddEmp 1, @p2, '測試部門老大', 'Marketing Specialist' ;

go

select * from EmployeeOrg