你的位置:首页 > ASP.net教程

[ASP.net教程]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) ;goCREATE 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)  COMMITEND ;go--插入根INSERT EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)VALUES (hierarchyid::GetRoot(), 1, '蓝灯', 'Marketing Manager') go--随机数declare @p1 intselect @p1=convert(int, 100000000*rand())declare @p2 intselect @p2=convert(int, 100000000*rand())--插入软件部门子集EXEC AddEmp 1, @p1, '研发部门老大', 'Marketing Specialist' ;EXEC AddEmp 1, @p2, '测试部门老大', 'Marketing Specialist' ;goselect * from EmployeeOrg