3

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.

Improve this question
4
  • You cannot reference the cte from within the cte. Commented Jun 18, 2010 at 20:39
  • why do you need select COUNT(*) from months? and why do you need a dynamic query? Commented Jun 18, 2010 at 20:41
  • If I dont put select count(*) it complains 'CTE not used'. I am trying to create a pivot query with months across Y axis.The number of months is variable. so I will have to create pivot as something like pivot(sum(fld) for col in(['jan-10'],['feb-10']...) Commented Jun 18, 2010 at 20:54
  • 1
    I think you may be overlooking the bigger message being sent by SQL Server: don't do dynamic crosstabs in T-SQL. The language was simply not made for it and you have to jump through lots of hoops to make it work. Use a reporting tool or do it in a middle-tier component. Commented Jun 18, 2010 at 21:03

4 Answers 4

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

No I tried a table variable but it looks like you cannot use a table variable in Dynamic SQL
dynamic sql is executed in a different scope. Basically, is a function call. It cannot reference variables from your current context. You must pass in any variable you want to use from current scope/context as an parameter, using exec sp_executesql @DynSql, '@parameterName type', @localTVP.
See msdn.microsoft.com/en-us/library/bb510489.aspx for an example how to pass a local table @variable to a procedure.
I created a table variable with CTE and tried to pass into sp_execsql but it does not accept table variables.
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)
Improve this answer

2 Comments

I have already tried this. I need to use pivot to display monthly data across the page.I am trying to create a pivot query with months across Y axis.The number of months is variable. so I will have to create pivot as something like pivot(sum(fld) for col in(['jan-10'],['feb-10']...). So I have to create the list of months as part of the dynamic query.But the pivot query cross joins with the months CTE.So I need to define the months CTE outside dynamic SQL and inside it too.I tried to insert months into a table variable and pass it to sp_execsql but it does not take accept table variables
Yes, without a view, you will need to create the CTE inside the dynamic SQL. I don't understand what the problem is. Maybe posting more of your problem, with error message would help.
0

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)
Improve this answer

3 Comments

No my problem is not selecting count(*) from cte but using CTE in Dynamic Sql.
Well now I see you are trying to use the Pivot(). With the data you provided, you will get one point of reference. What are you trying to compare it with? A little more info and I can use the cte.
The commented portion is for the pivot which I cannot get to work at present.
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.