Can I use multiple "with"?

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?

---

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.

---

Yes - just do it this way:

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

You don't need to repeat the WITH keyword

---

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 ".