Recursive SQL using CTE in SQL Server 2005

How to do a recursive query in SQL Server 2005, Recursive in the terms of you got a table where it has foreign key to the other record on the same table. Assume a table employee where a record on that table can be an employee or a manager which is an employee as well, or let’s call it when you have nested structure within one table, call it as a tree structure. I’ve googled and found the way in doing it so I believe it’s worthy for me to share with everyone

Table Schema

How to get the whole structure by using one SQL query, we can utilize CTE(Common table expression) and using UNION ALL to do the recursive and do the INNER JOIN with the CTE it self

--get all contentPageID for the page and its children
WITH dynamicPages(contentPage_id, parentContent_id, contentPageName) AS
	(
		SELECT
			c.contentPage_id, c.parentContent_id, c.ContentPageName
		FROM
			tblContentPage c
		WHERE
			c.contentPage_id = @contentPage_id
		UNION ALL
		SELECT
			c.contentPage_id, c.parentContent_id, c.contentPageName
		FROM
			tblContentPage c
		INNER JOIN
			dynamicPages b
		ON
			c.parentContent_id = b.contentPage_id
	)
SELECT * FROM dynamicPages