Can I use multiple "with"?

Question

Just for example:

With DependencedIncidents AS
(
    SELECT INC.[RecTime],INC.[SQL] AS [str] FROM
    (
        SELECT A.[RecTime] As [RecTime],X.[SQL] As [SQL] FROM [EventView] AS A 
        CROSS JOIN [Incident] AS X
            WHERE
                patindex('%' + A.[Col] + '%', X.[SQL]) > 0
    ) AS INC
)

With lalala AS
(
    SELECT INC.[RecTime],INC.[SQL] AS [str] FROM
    (
        SELECT A.[RecTime] As [RecTime],X.[SQL] As [SQL] FROM [EventView] AS A 
        CROSS JOIN [Incident] AS X
            WHERE
                patindex('%' + A.[Col] + '%', X.[SQL]) > 0
    ) AS INC
)

...doesn't work. "Error near With".

Also, I want to use first with inside second with. Is it real or I need to use temp tables?

BOL has the exact syntax, too. http://msdn.microsoft.com/en-us/library/ms175972%28v=SQL.100%29.aspx Notice `[ ,...n ]`. — user, Mar 21 '11 at 09:22

score 555 · Accepted Answer · answered Mar 21 '11 at 09:21

555

Try:

With DependencedIncidents AS
(
    SELECT INC.[RecTime],INC.[SQL] AS [str] FROM
    (
        SELECT A.[RecTime] As [RecTime],X.[SQL] As [SQL] FROM [EventView] AS A 
        CROSS JOIN [Incident] AS X
            WHERE
                patindex('%' + A.[Col] + '%', X.[SQL]) > 0
    ) AS INC
),
lalala AS
(
    SELECT INC.[RecTime],INC.[SQL] AS [str] FROM
    (
        SELECT A.[RecTime] As [RecTime],X.[SQL] As [SQL] FROM [EventView] AS A 
        CROSS JOIN [Incident] AS X
            WHERE
                patindex('%' + A.[Col] + '%', X.[SQL]) > 0
    ) AS INC
)

And yes, you can reference common table expression inside common table expression definition. Even recursively. Which leads to some very neat tricks.

answered Mar 21 '11 at 09:21

Tomek Szpakowicz

14,063
3
33
55

88

For those that didn't immediately notice it like myself, the catch here is adding a comma after the original with statement.. lol – CRSouser Sep 24 '17 at 15:35
59

and not writing the word `with` again – user230910 Apr 30 '18 at 22:30
Hi, is this equivalent to a Cross Join between two tables. Or does this create two separate tables. I wouldn't want to cross join two very large table, is there a way to efficiently create two separate "with" tables – Long Le Dec 06 '18 at 01:25
2

@LongLe No, they are not equivalent to joins and they are not tables. These are CTEs - Common Table Expressions. They are more like... named queries that you can use as if they were tables... or rather more like views. Please google them. They are neat. It is one of the best standard SQL features, helping enormously to keep complex queries understandable and under control. – Tomek Szpakowicz Dec 06 '18 at 20:46
For those who don't know what 'common table expressions' are, they are 'DependencedIncidents' and 'lalala' in the example given. For detail, see https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017. – Henry Yang Jun 02 '19 at 23:34
I also think it's good practice to start WITH statement with a semicolon – Agneum Oct 25 '19 at 06:19

score 201 · Answer 2 · edited Nov 24 '15 at 19:57

201

Yes - just do it this way:

WITH DependencedIncidents AS
(
  ....
),  
lalala AS
(
  ....
)

You don't need to repeat the WITH keyword

edited Nov 24 '15 at 19:57

ivan_pozdeev

33,874
19
107
152

answered Mar 21 '11 at 09:19

marc_s

732,580
175
1,330
1,459

21

Can lalala use DependencedIncidents? – Bren Apr 04 '16 at 23:58
1

Can DependencedIncidents use lalala? – Henry Yang Jun 02 '19 at 23:35
10

@HenryYang: no - the later CTE (`lalala`) can use any CTE defined prior to it - but the earlier one cannot use a CTE that is only going to be defined later on.... – marc_s Jun 03 '19 at 03:43

score 1 · Answer 3 · answered Feb 16 '23 at 11:31

You can solve this problem by using the temp table,

WITH DependencedIncidents AS
(
  ....
)

select * INTO #TempTable from DependencedIncidents 
  
with lalala AS
(
  ....
)

select * from lalala

you can also write the second with after the with . You can write a second "with" using ",".

After you finish all of them, you need to run "with" last return value "select * from lalala ".

Can I use multiple "with"?

3 Answers3

Linked

Related