I like the way the article was written, but the examples were trivial ones that could easily be done with a simple select. The recursive article linked to in the comments had great examples. I tried writing/modifying this as a test, to see if I understand how the recursion works:

WITH Ancestor (PersonID, ChildID, Name, SortKey) AS

(

-- Create the anchor query. This establishes the starting

-- point

SELECT

p.PersonID, p.ChildID, p.Name, CAST(p.SortKey + CAST (p.ChildID AS BINARY(4)) AS VARBINARY(900))

FROM dbo.Person p

Where p.PersonID = @root

UNION ALL

-- Create the recursive query. This query will be executed

-- until it returns no more rows

SELECT

p.PersonID, p.ChildID, p.Name, CAST(p.SortKey + CAST (p.ChildID AS BINARY(4)) AS VARBINARY(900))

FROM Ancestor a

INNER JOIN Person p on a.ChildID = p.PersonID

)

SELECT * FROM Ancestor ORDER BY SortKey

Note the reference to the CTE from within itself, with the first part of the union making up a seed table and the next part of the union feeding on the seed, then itself until no more rows are returned. That's pretty clever. And it's ANSI standard, (While "while" isn't, right?) so who can complain?

cl