SQL Server 2008 Dynamic Query using CTE -

I'm trying to write a dynamic query that uses CTE but I'm having problems - see below Is a simplified case

  declare @dinsal varchar (max) = ''; Declared as @cnt integer; Select months (months, 1, STDT) month (where month, 1, STDT) and cast ('06 / 30/2011) with months. 'AS DATE') Select COUNT (*) for months @ DynSql = 'Select from months' * exec (@DynSql)  

This does not work - I get the error invalid object Is the name 'month'

Is there any way of getting what I need? If I use floating table or table variable, will it work?

Your dynamic SQL reference can not do the month . A CTE has a single statement :

  Select from CT (CT Definition) as CT;  

If you want to reuse the results or definition of CTE, then you have to define the CTE again every time you want to use it (eg. Dyns in SQL) This can result in the table and reuse the table @ vararyable.