title: How can I have multiple common table expressions in a single SELECT statement?
body: <p>I am in the process of simplifying a complicated select statement, so thought I would use common table expressions.</p>
  
  <p>Declaring a single cte works fine.</p>
  
  <pre><code>WITH cte1 AS (
      SELECT * from cdr.Location
      )
  
  select * from cte1 
  </code></pre>
  
  <p>Is it possible to declare and use more than one cte in the same SELECT?</p>
  
  <p>ie this sql gives an error</p>
  
  <pre><code>WITH cte1 as (
      SELECT * from cdr.Location
  )
  
  WITH cte2 as (
      SELECT * from cdr.Location
  )
  
  select * from cte1    
  union     
  select * from cte2
  </code></pre>
  
  <p>the error is</p>
  
  <pre><code>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 xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
  </code></pre>
  
  <p>NB. I have tried putting semicolons in and get this error</p>
  
  <pre><code>Msg 102, Level 15, State 1, Line 5
  Incorrect syntax near ';'.
  Msg 102, Level 15, State 1, Line 9
  Incorrect syntax near ';'.
  </code></pre>
  
  <p>Probably not relevant but this is on SQL 2008.</p>