However it suprises me a bit that you would prefer this over the simple approach of using a temp table. I would not consider this alternative if it were only for the reason that it is it is less readable of what is going on (A common problem with CTE's anyways).
OTOH, it is a good example of how to impress other people not familiar with CTEs and implementing a simple problem in a difficult way. (TEST: give the two solutions to any SQL developer and ask them to figure out what is going on). Walter, this isn't an example of trying to impress someone.
I don't agree with your assessment that it's less readable. I'm sorry, but you just seem to have instinctive prejudices that newer features in SQL Server are less readable, more complicated, etc.
The first time I ever saw a CTE/recursive sample, I knew what was going on.
If you gave both solutions to a developer who understands CTEs and recursive queries, they'll certainly be able to tell you.
And even if you give both solutions to a good developer who hasn't yet been exposed to CTEs and recursive queries, the developer might take a look at the CTE/recursive query and start to think it might be a better way to solve recursive queries.
A follow-up question. Suppose you have a simple table that stores parentID pointers...
DECLARE @tProducts TABLE (ID int, Name char(50), ParentID int)
INSERT INTO @tProducts VALUES (1, 'Brand 1', null)
INSERT INTO @tProducts VALUES (2, 'Brand 2', null)
INSERT INTO @tProducts VALUES (3, 'Brand 3', null)
INSERT INTO @tProducts VALUES (6, 'Brand 1, Group 1', 1)
INSERT INTO @tProducts VALUES (7, 'Brand 1, Group 2', 1)
INSERT INTO @tProducts VALUES (8, 'Brand 1, Group 3', 1)
INSERT INTO @tProducts VALUES ( 9, 'Brand 2, Group 1', 2)
INSERT INTO @tProducts VALUES (10, 'Brand 2, Group 2', 2)
INSERT INTO @tProducts VALUES ( 11, 'Brand 3, Group 3', 3)
INSERT INTO @tProducts VALUES (12, 'Brand 1, Group 1, Item 1', 6)
INSERT INTO @tProducts VALUES (13, 'Brand 1, Group 1, Item 2', 6)
INSERT INTO @tProducts VALUES (14, 'Brand 1, Group 1, Item 1, SKU 1', 12)
Now, suppose that for a particular row, you wanted to know all parents, or all children.
Kevin
