Nest With clause inside a select statement

common-table-expression, sql, sql-server

Solution

You can use multiple CTEs by separating them with a comma, e.g:

WITH T(date) AS 
(
    SELECT @StartDate 
    UNION ALL
    SELECT DateAdd(day,1,T.date)  
    FROM T
    WHERE datediff(dd,T.date , @EndDate)>0 

), T2 AS
(
    SELECT date 
    FROM T 
    OPTION (MAXRECURSION 32767)
)
select * from t2
join 
    (select * from SomeTable where MyDate between @StartDate and @EndDate)
on //Some condition

For what it's worth though, using a recursive CTE to generate a list of dates is not the best way. The best way is to have a static calendar table, failing this you can generate a set of dates on the fly as follows:

SELECT  TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @StartDate)
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b;

This will be more efficient than looping through dates. For more information see:

- Generate a set or sequence without loops – part 1

- Generate a set or sequence without loops – part 2

- Generate a set or sequence without loops – part 3

Problem

I have a recursive query using Common Table Expressions which gets the range of dates between a start and end date ``` WITH T(date) AS ( SELECT @StartDate UNION ALL SELECT DateAdd(day,1,T.date) FROM T WHERE datediff(dd,T.date , @EndDate)>0 ) SELECT date FROM T OPTION (MAXRECURSION 32767)) ``` Is there any way for me to nest this within another select statement without creating a temporary table? I'm looking for a statement like so ``` select * from (WITH T(date) AS ( SELECT @StartDate UNION ALL SELECT DateAdd(day,1,T.date) FROM T WHERE datediff(dd,T.date , @EndDate)>0 ) SELECT date FROM T OPTION (MAXRECURSION 32767))) join (select * from SomeTable where MyDate between @StartDate and @EndDate) on //Some condition ``` I've tried this out in SQL Server and there is an Incorrect Syntax near WITH error being thrown. By definition, CTE only exists within the scope of the query. So, is it necessary that a Temporary table is necessary to store the results of the CTE or can the above scenario also work?

Original source