CTE's have their place as long as you are not dealing with a large volume of data. One of the problems with CTEs is performance tuning. Unless I'm missing something there is no way to set primary keys or index the CTE. I've used CTE's in applications where I'm dealing with 1k-5k rows of data. Beyond that I'll use a temp table.
Thanks for the information.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman