nested WITH statement

Question

That works:

; with res1 as (
        select 1 as col1
    )
select * from res1

How to nest a WITH statement one inside another? I tried

; with res1 as (
    ; with res2 as (
        select 1 as col1
    ) 
    select * from res2
    )
select * from res1

but I get an error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.

What exactly are you trying to do? Maybe there is a different way. — Michał Powaga, Mar 05 '12 at 07:09
Please don't prefix your titles with "MSSQL" and such. That's what the tags are for. — John Saunders, Mar 20 '12 at 16:29
https://stackoverflow.com/q/1413516/6655648 is a very similar question. — Pablo H, Dec 08 '17 at 22:48

score 8 · Accepted Answer · answered Mar 05 '12 at 06:52

8

you can do it as follows:

; with res1 as (
        select 1 as col1
    ),
    res2 as(
    select * from res1
    )
select * from res2

answered Mar 05 '12 at 06:52

Vikram

1 Answers1