Comprehensive Guide to Multiple WITH Statements and Nested CTEs in SQL

Basic Syntax Structure of Multiple WITH Statements

In SQL Server, when using multiple WITH statements to define Common Table Expressions, specific syntax rules must be followed. According to the best answer from the Q&A data, the correct syntax requires all CTE definitions to follow a single WITH keyword, separated by commas between expression definitions.

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
)
SELECT * FROM DependencedIncidents
UNION ALL
SELECT * FROM lalala;

Common Error Analysis and Solutions

The "Error near With" encountered in the original question stems from repeated use of the WITH keyword. In SQL syntax, the WITH clause can only appear once, and subsequent CTE definitions must be connected to the previous CTE using commas. This design ensures logical clarity and syntactic consistency in queries.

Error example demonstrating incorrect syntax:

-- Incorrect approach: duplicate WITH keywords
With DependencedIncidents AS (...)
With lalala AS (...)

Correct approach combining all CTE definitions:

-- Correct approach: single WITH, multiple CTEs comma-separated
WITH DependencedIncidents AS (...),
lalala AS (...)

CTE Nesting Reference Mechanism

Common Table Expressions support referencing previously defined CTEs in subsequent CTEs. This nesting reference mechanism significantly enhances the expressive power of SQL queries. As described in the reference article, this design allows for building complex data processing pipelines.

The following example demonstrates dependency relationships between CTEs:

WITH base_data AS (
    SELECT country, product_family, 
           SUM(qty_exported * unit_price) AS total_exports
    FROM product
    GROUP BY country, product_family
),
country_totals AS (
    SELECT country, SUM(total_exports) AS total_exports_country
    FROM base_data
    GROUP BY country
)
SELECT bd.country, bd.product_family, bd.total_exports,
       ROUND((bd.total_exports / ct.total_exports_country) * 100, 2) AS percentage
FROM base_data bd
JOIN country_totals ct ON bd.country = ct.country;

Advanced Applications of Recursive CTEs

One of the most powerful features of CTEs is support for recursive queries, which is particularly important when processing hierarchical data. Recursive CTEs consist of anchor members and recursive members connected by UNION ALL.

Employee hierarchy query example:

WITH RECURSIVE company_hierarchy AS (
    -- Anchor member: top-level managers
    SELECT employee_id, firstname, lastname, 
           manager_employee_id, 0 AS hierarchy_level
    FROM employees
    WHERE manager_employee_id IS NULL
    
    UNION ALL
    
    -- Recursive member: subordinate employees
    SELECT e.employee_id, e.firstname, e.lastname,
           e.manager_employee_id, ch.hierarchy_level + 1
    FROM employees e
    INNER JOIN company_hierarchy ch ON e.manager_employee_id = ch.employee_id
)
SELECT * FROM company_hierarchy
ORDER BY hierarchy_level, employee_id;

Performance Considerations and Best Practices

While CTEs provide excellent code readability and modularization capabilities, they should be used cautiously in performance-sensitive scenarios. CTEs are re-executed each time they are referenced, which differs from the one-time creation of temporary tables. For complex multi-level nested CTEs, it is recommended to:

• Limit CTE nesting depth to avoid overly complex query structures
• Consider using temporary tables in scenarios requiring multiple references
• Optimize CTE query performance through appropriate indexing
• Analyze query execution paths using EXPLAIN PLAN

Practical Application Scenario Analysis

Based on the specific case in the Q&A data, we can observe the practical application of multiple CTEs in event analysis. The original query implements event correlation analysis based on pattern matching through CROSS JOIN and patindex functions. This architecture holds significant value in log analysis and security monitoring scenarios.

Optimized query structure:

WITH EventPatterns AS (
    SELECT A.[RecTime], A.[Col], X.[SQL]
    FROM [EventView] AS A
    CROSS JOIN [Incident] AS X
    WHERE patindex('%' + A.[Col] + '%', X.[SQL]) > 0
),
CategorizedIncidents AS (
    SELECT [RecTime], [SQL] AS incident_details,
           CASE 
               WHEN [SQL] LIKE '%error%' THEN 'Error'
               WHEN [SQL] LIKE '%warning%' THEN 'Warning'
               ELSE 'Info'
           END AS severity
    FROM EventPatterns
)
SELECT * FROM CategorizedIncidents
ORDER BY [RecTime] DESC;

Through reasonable CTE layered design, complex data processing logic can be decomposed into multiple clear steps, significantly improving code maintainability and readability.