Use Multiple CTE

Question

Cannot figure out how to use multiple CTE

This fails

; with [cteOne] as (
  select 1 as col
),
  [cteTwo]  as (
  select 2 as col
)
select 'yesA' where exists (select * from [cteOne])
select 'yexB' where exists (select * from [cteTwo])

This works - but this is not what I need

; with [cteOne] as (
  select 1 as col
),
  [cteTwo]  as (
  select 2 as col
)
select * from [cteOne]
union 
select * from [cteTwo]

The real syntax was a join to row_number() partition
I just ended up using a derived table

Your example doesn't need multiple CTEs. You could use two single ones. Can you show something more representative of why you need it? In any event they are only in scope for the single statement immediately following. You can't return 2 separate result sets from one definition. — Martin Smith, Sep 20 '13 at 19:56
What is it what you need? Please post the resultset you expected to see — Quassnoi, Sep 20 '13 at 19:59
What are you trying to achieve? Why can't you just put a `union` into the first example? — , Sep 20 '13 at 20:08
I know this sample does not need multiple CTE. I have more complex tsql that needs a format like the first. — paparazzo, Sep 20 '13 at 20:11
Well it isn't possible and as we have no idea what the "more complex" case is we can't suggest the best alternative. If you need to reference a CTE in multiple statements you can just repeat it or create a view instead. Or maybe the statements could be combined. Or maybe it should be materialized into a temp table. — Martin Smith, Sep 20 '13 at 20:16
@MartinSmith I need TSQL syntax. I just substituted an inline derived table. — paparazzo, Sep 20 '13 at 21:21
For anyone else who was searching for information on how to use multiple CTEs in one query, this question/answer may be of some use: http://stackoverflow.com/questions/2140750/keeping-it-simple-and-how-to-do-multiple-cte-in-a-query — marknuzz, Dec 23 '14 at 01:11

score 10 · Accepted Answer · answered Sep 20 '13 at 20:23

10

The first one fails because a CTE or set of CTEs can only be followed by a single statement.

You could rewrite it as

; with [cteOne] as (
  select 1 as col
)
select 'yesA' where exists (select * from [cteOne])

; with [cteTwo]  as (
  select 2 as col
)
select 'yexB' where exists (select * from [cteTwo])

answered Sep 20 '13 at 20:23

podiluska

50,950
7
98
104

I came to the same conclusion. I definitely don't like that behavior. – paparazzo Sep 20 '13 at 21:18
Why is it required to have the colon before the with statement? ";WITH" Thanks – Drewdin Jan 21 '15 at 20:14
3

@Drewdin It's not, technically. But it is required to have a semi-colon after the previous statement, unlike many other statements, so a semi-colon before the with ensures that if you're not used to using semi-colon separators. – podiluska Jan 22 '15 at 21:29
1

Thanks, i have seen it a few times and didn't understand why – Drewdin Jan 22 '15 at 23:23

Use Multiple CTE

1 Answers1

Linked