How we can use CTE in subquery in sql server?

Question

How we can use CTE in subquery in sql server?

like ..

select id (i want to use CTE here), name from table_name

Answer 1

Just define your CTE on top and access it in the subquery?

WITH YourCTE(blubb) AS
(
    SELECT 'Blubb'
)
SELECT id,
       (SELECT blubb FROM YourCTE),
       name
FROM   table_name

Answer 2

There is a very good article on CTE's here (http://www.4guysfromrolla.com/webtech/071906-1.shtml). I have referred back to this article many times in learning how to get the most from CTE's.

Randy