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

Nope, you can only have one CTE in a statement as I found out the other day.

EDIT: And that WITH statement is a Common Table Expression, very handy feature.

Answer 2

You may be able to us a series of sub-queries. Or sub-queries nested in a CTE.

An example of sub-queries using Northwind:

SELECT * FROM 
    (SELECT * FROM
        (SELECT * FROM dbo.Employees WHERE Country = 'USA') as TableFiltered1
     ) AS TableFilterd2 WHERE City = 'Seattle'

You can use two CTEs, but maybe not the way you wanted to, see:
http://www.4guysfromrolla.com/webtech/071906-1.shtml

Answer 3

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.