Can I use WITH in TSQL twice to filter a result set like my example?

Question

I need to do something like this but SQL Server 2008 doesn't like it. My query is actually more complex than this and I realize this isn't the best way to accomplish what I'm doing but my focus is on the functionality of the WITH statement and not the select and where statements.

WITH stuff1 AS ( select name, startdate, id from employees where startdate > 0 )

WITH stuff2 AS ( select name, startdate, id from stuff1 )

select * from stuff2 where id > 10

Answer 1

I do it all the time:

WITH stuff1 AS (
    SELECT name
           ,startdate
           ,id
    FROM employees
    WHERE startdate > 0
)
,stuff2 AS (
    SELECT name
           ,startdate
           ,id
    FROM stuff1
)
SELECT *
FROM stuff2
WHERE id > 10

As far as I can tell, I haven't reached a limit in CTEs.

The only thing you can't do (which would be pretty useful) is reuse CTEs in separate SELECTs:

WITH stuff1 AS (
    SELECT name
           ,startdate
           ,id
    FROM employees
    WHERE startdate > 0
)
,stuff2 AS (
    SELECT name
           ,startdate
           ,id
    FROM stuff1
)
SELECT *
FROM stuff2
WHERE id > 10
;
SELECT *
FROM stuff2
WHERE id < 10

Say. Instead you have to copy and paste the entire CTE chain again.