Neil,

Awesome code - thanks! Still trying to get my head around it - lol.

A suggestion: I was looking for a way to get the manager's name as well as their ID in the results and came up with the following:

1) Set the big boss' Reports_To_ID = to his Employee_ID, instead of null; i.e. change:

INSERT INTO @Employees

SELECT 1,'Roy Hodgson',NULL,'Managing Director'

to

INSERT INTO @Employees

SELECT 1,'Roy Hodgson',1,'Managing Director'

2) Change the final select statement to:

SELECT a.Employee_ID ,

a.Employee_Name ,

a.Reports_To_ID ,

b.Employee_Name Manager_Name,

a.Job_Title

FROM _hirearchy a JOIN _hirearchy b ON b.Employee_ID = a.Reports_To_ID

I've attached my code for your / everyone's benefit to see the changes.

The only problem I see with this is if you change the @BossID to a different value (7248 for Kyle Walker, for example), he doesn't show up in the results as an Employee. Can you think of a way to change your query to return the Manager_Name? That would be awesome!

Kudos again,

Mike