title: SQL Server 2008 Dynamic Query using CTE
body: <p>I am trying to write a Dynamic Query which uses a CTE. But I am facing problems - see below
  This is a simplified case</p>
  
  <pre><code>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)&lt;CAST('06/30/2011' AS DATE)
  )
  select COUNT(*) from months
  set @DynSql='select * from months'
  exec (@DynSql)
  </code></pre>
  
  <p>This does not work - the error I get is 
  Invalid Object name 'Months'</p>
  
  <p>Is there any way of achieving what I want. Will it work if I use Temp table or table variable.</p>