Add/insert managers under CEO in an Employees table by using HIRARCHYID column

Posted by Professionaluser under Sql Server category on | Points: 40 | Views : 355
DECLARE @GenMgr hierarchyid
SELECT @GenMgr = [HierarchyID]FROM Employees WHERE EmployeeID = 1
INSERT INTO Employees ([HierarchyID], EmployeeID, EmployeeName, Title)
VALUES (@GenMgr.GetDescendant(NULL, NULL) , 2, 'Jane', 'General Manager') ;

SELECT [HierarchyID].ToString() as HierarchyIDStr, [HierarchyID], EmployeeID, EmployeeName, Title
FROM Employees

HierarchyIDStr	HierarchyID	EmployeeID	EmployeeName	Title
/ (blank) 1 Nick CEO
/1/ 0x58 2 Jane General Manager

If parent is not NULL, and both child1 and child2 are NULL in GetDescendant() function, returns a child of parent.

Comments or Responses

Login to post response