Jack-408956 (8/3/2010)
None of this "with" stuff is working for us on MSSQL 2000...Server: Msg 156, Level 15, State 1, Procedure subordinates, Line 2
Incorrect syntax near the keyword 'with'.
Is it a feature of MSSQL 2005 only?
Yes... "WITH" is a precursor to CTE's.
If you want to do this "stuff" in 2k, do an initial insert similar to the first part (above the UNION) of the CTE. Then, do a loop that does very similar to the second part of the CTE which will have nearly the same speed as the CTE and (haven't tried it with this particular table) will likely use about 1/3rd the number of reads. The second part of the CTE (or the loop) is NOT RBAR, in this case. Each iteration of the loop loads an entire level of the hierarchy no matter how wide a given level becomes.
That's all a recursive CTE does, by the way... it does an "initial load" and then loops the same way (and with the same impact on performance) as a While Loop.
In 2k, this method will give much better utility and performance than using the classic "Expanding Hierarchies" example given in 2k Books Online.
--Jeff Moden
Change is inevitable... Change for the better is not.