Thanks for the article, makes me look forward to our upcoming migration from 2000 to 2008.

However, I have to ask why you would actually try to use recursion for the 2000 example? Especially with the well-known issues around nesting, why provide such a solution? When I need to do hierarchies, I use something more like this:

create procedure EmployeeHierarchy

@EmpID int

as

declare @out table (EmpID int, EmpName varchar(255), ReportsTo int)

insert into @out

selectEmpID, EmpName, ReportsTo

fromEmployees_2000_2005

whereEmpID = @EmpID

while @@rowcount > 0

insert into @out

selectEmpID, EmpName, ReportsTo

fromEmployees_2000_2005

whereReportsTo in (select EmpID from @out)

and EmpID not in (select EmpID from @out)

select o.EmpID, o.EmpName, e.EmpName Manager

from @out o

inner joinEmployees_2000_2005 e

ono.ReportsTo = e.EmpID

order by 1

go

A routine like this self-limits, so no infinite loops in cases when somebody messes up the list. No problems with recursion levels either. If the @out table gets too big, convert it to a temp table with indexes.

Or am I just missing something?

Again, thanks for the article!