Wednesday, June 8, 2011

Example on HierarchyID DataType

CREATE TABLE Employee
(EmployeeID int IDENTITY (1,1) PRIMARY KEY,
LastName nvarchar (50),
FirstName nvarchar (50),
Title nvarchar (50),
HireDate datetime,
PhoneExtention int,
EmailAddress varchar (255),
NodeRec hierarchyid)
GO

DECLARE @NodeRec hierarchyid
set @NodeRec = hierarchyid::GetRoot()
INSERT INTO Employee (LastName, FirstName, HireDate, Title, PhoneExtention, EmailAddress, NodeRec)
values
('Smith', 'Frank', '1/17/2005', 'CEO', 65428,'frank.smith@northwindtraders.com', @NodeRec.GetDescendant(null, null))
GO

select * from Employee

DECLARE @Manager hierarchyID, @Employee hierarchyID
SELECT @Manager = NodeRec FROM Employee WHERE EmployeeID = 1

SELECT @Employee = max(NodeRec)FROM Employee WHERE NodeRec.GetAncestor(1) = @Manager

INSERT INTO Employee(LastName, FirstName, HireDate, Title, PhoneExtention, EmailAddress, NodeRec)
VALUES
('Jefferson', 'John', '3/23/2006', 'VP Sales', 65647,'john.jefferson@northwindtraders.com', @Manager.GetDescendant(@Employee,null))

select * from Employee

SELECT @Employee = max(NodeRec)FROM Employee WHERE NodeRec.GetAncestor(1) = @Manager

INSERT INTO Employee
(LastName, FirstName, HireDate, Title, PhoneExtention, EmailAddress, NodeRec)
VALUES
('Karls', 'Kristin', '8/2/2006', 'VP IT', 63423,'kristin.karls@northwindtraders.com', @Manager.GetDescendant(@Employee,null))

select * from Employee
select EmployeeId, NodeRec, NodeRec.ToString()'Employee Path', LastName,FirstName from Employee

GO

CREATE PROCEDURE usp_AddEmployee
@SupervisorID int,
@FirstName nvarchar (50),
@LastName nvarchar (50),
@HireDate date,
@Title nvarchar (50),
@PhoneExtention int,
@EmailAddress varchar (255)
AS
BEGIN
DECLARE @Manager hierarchyID, @Employee hierarchyID
SELECT @Manager = NodeRec FROM Employee
WHERE EmployeeID = @SupervisorID

SELECT @Employee = max(NodeRec)FROM Employee
WHERE NodeRec.GetAncestor(1) = @Manager

INSERT INTO Employee
(LastName, FirstName, HireDate, Title, PhoneExtention, EmailAddress,NodeRec)
VALUES
(@LastName, @FirstName, @HireDate, @Title, @PhoneExtention,@EmailAddress, @Manager.GetDescendant(@Employee, null))
END
GO

exec usp_AddEmployee 3, 'Janice', 'Bing', '8/12/2004', 'Development Manager',53672, 'janice.bing@northwindtraders.com'
exec usp_AddEmployee 3, 'Jim', 'Frankenfurter', '6/30/2007', 'NOC Manager',76522, 'jim.frankerfurter@northwindtraders.com'
exec usp_AddEmployee 5, 'John', 'Willis', '7/3/2002', 'NOC Tech', 65242,'john.willis@northwindtraders.com'
exec usp_AddEmployee 5, 'Sarah', 'Jones', '8/4/2002', 'NOC Tech', 73625,'sarah.jones@northwindtraders.com'
exec usp_AddEmployee 5, 'Fred', 'Matt', '5/3/2004', 'NOC Tech', 26253,'fred.matt@northwindtraders.com'
exec usp_AddEmployee 2, 'Chris', 'Marshall', '2/5/2003', 'Sales Manager',82756, 'chris.marshall@northwindtraders.com'
exec usp_AddEmployee 8, 'Bob', 'Harris', '4/3/2003', 'Inside Sales', 63527,'bob.harris@northwindtraders.com'

GO

SELECT * FROM EMPLOYEE
SELECT EmployeeId, NodeRec, NodeRec.ToString()'Employee Path', LastName,FirstName from Employee
GO