Sql-server – Nested with – Incorrect syntax near the keyword ‘WITH’

sql server

With inside with

This is ok

    WITH CS AS (
      SELECT      'ab' as a
    )
    SELECT * from cs

http://data.stackexchange.com/mathematics/revision/138658/170321/comment-partition-by-question

But, as soon as I wrap it with a second with, 

with PS as (
        WITH CS AS (
          SELECT      'ab' as a
        )
        SELECT * from cs
        ) select * from PS

I get this error,

Incorrect syntax near the keyword 'WITH'. 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. Incorrect syntax near ')'.

With inside select

The following is ok

select ab.a from (
  select a from (SELECT 'ab' as a) b
            ) ab

This is also runs

  with cs as (SELECT 'ab' as a)
  select a from cs

But, wrapping expression with another select, as this one proposes, 

  select a from (
    with cs as (SELECT 'ab' as a)
    select a from cs
  ) ab

fails again with the same error.

I do it in data.stackexchange and they report that they use MSSQL

Best Answer

From documentation

Specifying more than one WITH clause in a CTE is not allowed. For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

Thus, 

with PS as (
    WITH CS AS (
      SELECT      'ab' as a
    )
    SELECT * from cs
    ) select * from PS

gives you error. You can define multiple CTEs though : 

WITH CS AS (SELECT 'ab' as a),
PS as (SELECT * from cs) 
select * from PS
Related Question