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
