Microsoft® SQL Server® 2012 Bible

332

Part III: Advanced T-SQL Data Types Querying Techniques

DECLARE @CurrentEmployee hierarchyid

SELECT @CurrentEmployee = OrganizationNode

FROM HumanResources.Employee

WHERE OrganizationNode = '/5/'

SELECT OrganizationNode.ToString() AS 'Hierarchy',

p.FirstName + ' ' + p.LastName AS 'Name',

e.OrganizationLevel,

e.JobTitle

FROM HumanResources.Employee e

INNER JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID

WHERE OrganizationNode.GetAncestor(1) = @CurrentEmployee

Hierarchy Name OrganizationLevel JobTitle

--------- ---- ----------------- --------

/5/1/ Stephanie Conroy 2 Network Manager

/5/2/ Karen Berg 2 Application Specialist

/5/3/ Ramesh Meyyappan 2 Application Specialist

/5/4/ Dan Bacon 2 Application Specialist

/5/5/ François Ajenstat 2 Database Administrator

/5/6/ Dan Wilson 2 Database Administrator

/5/7/ Janaina Bueno 2 Application Specialist

/5/1/ Gustavo Achong 2 Rock Star DBA

By changing the parameter value, you can change the results. In the following example the

parameter value changes from 1 to 2 to return the second-level hierarchy.

DECLARE @CurrentEmployee hierarchyid

SELECT @CurrentEmployee = OrganizationNode

FROM HumanResources.Employee

WHERE OrganizationNode = '/5/'

SELECT OrganizationNode.ToString() AS 'Hierarchy',

p.FirstName + ' ' + p.LastName AS 'Name',

e.OrganizationLevel,

e.JobTitle

FROM HumanResources.Employee e

INNER JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID

WHERE OrganizationNode.GetAncestor(2) = @CurrentEmployee

Hierarchy Name OrganizationLevel JobTitle

--------- ----- ----------------- --------

/5/1/1/ Ashvini Sharma 3 Network Administrator

/5/1/2/ Peter Connelly 3 Network Administrator

c13.indd 332c13.indd 332 7/30/2012 4:47:05 PM7/30/2012 4:47:05 PM

http://www.it-ebooks.info