SQL Server 2008 Dynamic Query Using CTE

Question

I am trying to write a dynamic query that uses CTE. But I ran into 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 doesn’t work - the error I get, Invalid object name "Months"

Is there a way to achieve what I want. Will it work if I use a Temp table or a table.

Answer 1

Your dynamic SQL cannot reference months. CTE volume is one operator:

with cte as (cte definiton) select from cte;

CTE, CTE , (, @DynSql), @variable @variable.

Answer 2

c , . select. sql months, . CTE , .

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) 

, , SQL, SQL .

---

, , (), , ( ).

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) 

Answer 3

Well, I got it to work, but I don’t understand its scope ...

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 

Now retype the 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 4

You cannot use this CTE or @TableVariable in dynamic sql, but you can use the #Temp table for this. Create a temporary table, save the data in it (you can copy the CTE result to the temp table) and use it in a dynamic query. This is the solution.