SQL Server 2008 Dynamic Query using CTE

Question

I am trying to write a Dynamic Query which uses a CTE. But I am facing problems - see below This is a simplified case

declare @DynSql varchar(max)='';
declare @cnt as integer;
with months as (
select CAST('07/01/2010' as DATE) stdt
UNION ALL
SELECT DATEADD(MONTH,1,STDT) FROM months
WHERE DATEADD(MONTH,1,STDT)<CAST('06/30/2011' AS DATE)
)
select COUNT(*) from months
set @DynSql='select * from months'
exec (@DynSql)

This does not work - the error I get is Invalid Object name 'Months'

Is there any way of achieving what I want. Will it work if I use Temp table or table variable.

Answer 1

Your dynamic SQL cannot reference months. The scope of a CTE is a single statement:

with cte as (cte definiton) select from cte;

If you want to re-use the CTE's result or definition, you have to either re-define the CTE every time you want to use it (eg. in the @DynSql) or materialize it's result into a table @variable and re-use the table @variable.

Answer 2

Well I got it to work, but I do not understand the scope of this...

declare @DynSql varchar(max)
declare @cnt as integer;
declare @stdt datetime;
Set @DynSql =''
Select @stdt = CAST('07/01/2010' as DATEtime);
with months as ( 
SELECT DATEADD(MONTH,1,@stdt) As [month] WHERE DATEADD(MONTH,1,@stdt)<CAST('06/30/2011' AS DATEtime) 
) 
select COUNT(*) from months 

Revision now that I have move information:

declare @DynSql varchar(max)
declare @cnt as integer;
declare @stdt datetime;
Set @DynSql = 'With ctemonths  as ('
Select @stdt = CAST('07/01/2010' as DATEtime);
Set @cnt = 1;
while @cnt <= 11 --(Select DateDiff(mm, @stdt, '06/30/2011'))
Begin
    IF (@CNT =1)
        Set @DynSql = @DynSql + 'Select DATEADD(MONTH,' + Cast(@cnt as nvarchar(2)) + ',''' + Convert(varchar(10), @stdt, 103) + ''') As [month] '
    eLSE
        Set @DynSql = @DynSql + 'UNION Select DATEADD(MONTH,' + Cast(@cnt as nvarchar

(2)) + ',''' + Convert(varchar(10), @stdt, 103) + ''') As [month] '
Set @cnt = @cnt + 1

End;

Set @DynSql = @DynSql + ') Select * from ctemonths' -- PIVOT (max([month]) for [month] in ([month]))'

exec (@DynSql)

Answer 3

The with keyword does not declare an object that can be referenced in later queries. It is part of the select query. Your dynamic sql was trying to reference an object months that did not exist. Include the CTE in the string defining the dyanic query.

declare @DynSql varchar(max)=''; 
set @DynSql=
'with months as ( 
    select CAST(''07/01/2010'' as DATE) stdt 
    UNION ALL 
    SELECT DATEADD(MONTH,1,STDT) FROM months 
    WHERE DATEADD(MONTH,1,STDT)<CAST(''06/30/2011'' AS DATE)) 
select * from months'
exec (@DynSql) 

However, I don't see what you gain by making the SQL dynamic, since nothing within the SQL statement varies.

---

If you want an object you can reference later, you could create a view (once) that would be used by your dynamic query, and similar queries (many times).

create view months_v as 
    with months as (select CAST('07/01/2010' as DATE) stdt 
        UNION ALL 
        SELECT DATEADD(MONTH,1,STDT) FROM months 
        WHERE DATEADD(MONTH,1,STDT)<CAST('06/30/2011' AS DATE)) 
    select * from months;
go

declare @DynSql varchar(max)='';
set @DynSql='select * from months_v' 
exec (@DynSql)