CODEWITHSUNDEEP
sqlsql-server
josephj1989
josephj1989

Reputation: 9709

SQL Server 2008 Dynamic Query using CTE

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.

Upvotes: 3

Views: 13014

Answers (4)

Abdul Rasheed
Abdul Rasheed

Reputation: 6729

You can't use that CTE or @TableVariable in a dynamic sql, but you can use a #Temp table for this. Create a temp table, store the data in that ( u can copy your CTE result to temp table) and use it in dynamic query. That is the solution.

Upvotes: 0

websch01ar
websch01ar

Reputation: 2123

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)

Upvotes: 0

Shannon Severance
Shannon Severance

Reputation: 18410

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)

Upvotes: 3

Remus Rusanu
Remus Rusanu

Reputation: 294377

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.

Upvotes: 3

Related Questions