How can I have multiple common table expressions in a single SELECT statement?

Question

I am in the process of simplifying a complicated select statement, so thought I would use common table expressions.

Declaring a single cte works fine.

WITH cte1 AS (

    SELECT * from cdr.Location

    )

select * from cte1 

Is it possible to declare and use more than one cte in the same SELECT?

ie this SQL gives an error

WITH cte1 as (

    SELECT * from cdr.Location

)

WITH cte2 as (

    SELECT * from cdr.Location

)

select * from cte1    

union     

select * from cte2

the error is

Msg 156, Level 15, State 1, Line 7

Incorrect syntax near the keyword 'WITH'.

Msg 319, Level 15, State 1, Line 7

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an XML namespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

NB. I have tried putting semicolons in and get this error

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near ';'.

Msg 102, Level 15, State 1, Line 9

Incorrect syntax near ';'.

Probably not relevant but this is on SQL 2008.

Answer 1

Try using the code given below:

WITH 

    cte1 as (SELECT * from cdr.Location),

    cte2 as (SELECT * from cdr.Location)

select * from cte1 union select * from cte2

In the above query, WITH is just a clause, and like the other clauses that take lists, "," is the appropriate delimiter.