CTE in Dynamic SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CTE in Dynamic SQL

Author

Topic

josephj1989
Starting Member

7 Posts

Posted - 2010-06-18 : 17:40:00

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. It works with temp table but not with table variable.I also tried to create a table variable from the CTE and pass it as parameter to sp_execsql but it does not accept table variables.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-06-19 : 08:59:33

what is your goal with this query?

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-06-19 : 09:04:40

[code]
with months_CTE as (
select CAST('07/01/2010' as DATE) stdt
UNION ALL
SELECT DATEADD(MONTH,1,STDT)
FROM months_CTE
WHERE DATEADD(MONTH,1,STDT)<CAST('06/30/2011' AS DATE)
)
select * into #CTE_tm from months_CTE

select count(*) from #CTE_tm
select * from #CTE_tm
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources