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