SQL [hierarchyid] type: How to dynamically insert hierarchical data, hierarchyid level

SQL [hierarchyid] type: How to dynamically insert hierarchical data, hierarchyid level

[Hierarchyid] is a good data type and provides a convenient and high-performance tree-based query. I have found a lot of information on the Internet and have not talked about how to perform dynamic insertion, I carefully read the document from MSDN and wrote a 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 root INSERT EmployeeOrg (OrgNode, EmployeeID, EmpName, Title) VALUES (hierarchyid :: getRoot (), 1, 'lamps, 'marketing manager') go -- random number declare @ p1 intselect @ p1 = convert (int, 100000000 * rand ()) declare @ p2 intselect @ p2 = convert (int, 100000000 * rand () -- Insert the software department subset EXEC AddEmp 1, @ p1, 'r & D department leader ', 'marketing specialist '; EXEC AddEmp 1, @ p2, 'test Team Leader ', 'marketing specialist'; goselect * from EmployeeOrg