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.