Menu
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
818
Having Multiple CTEs work only if you write the WITH keyword once. But this is not just anywhere you want. You have to write it before the first CTE. The second and any following CTE starts with the name of the respective CTEs, unlike the first CTE which starts with the WITH keyword.
A CTE name can be referenced in other CTEs, enabling CTEs to be defined based on other CTEs. A CTE can refer to itself to define a recursive CTE. Common applications of recursive CTEs include series generation and traversal of hierarchical or tree-structured data.
Below is the T-SQL for each of our test query types. Looking at SQL Profiler results from these queries (each were run 10 times and averages are below) we can see that the CTE just slightly outperforms both the temporary table and table variable queries when it comes to overall duration.
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])
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
