代码之家 › 专栏 › 技术社区 › josephj1989

使用CTE的sqlserver2008动态查询

sql-server sql

josephj1989 · 技术社区 · 16 年前

我正在尝试编写一个使用CTE的动态查询。但我面临着问题-见下文这是一个简化的案例

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)

这不起作用-我得到的错误是对象名“Months”无效

有没有办法达到我想要的。如果我使用Temp表或表变量,它会工作吗。

4 回复 | 直到 16 年前

Remus Rusanu 16 年前

动态SQL无法引用 months . CTE的范围是单一的陈述 :

with cte as (cte definiton) select from cte;

如果您想重用CTE的结果或定义,您必须在每次使用CTE时重新定义它(例如在@DynSql中),或者将其结果具体化为表@变量并重用表@变量。

Shannon Severance 16 年前

with关键字不声明可以在以后的查询中引用的对象。它是select查询的一部分。您的动态sql试图引用一个对象 months

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)

但是,我看不出使SQL动态化有什么好处,因为SQL语句中没有任何变化。

如果您想要一个以后可以引用的对象,您可以创建一个视图(一次)供动态查询和类似查询(多次)使用。

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)

websch01ar 16 年前

好吧,我让它工作,但我不明白这个范围。。。

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

现在我有了移动信息:

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)

Abdul Rasheed 10 年前

在动态sql中不能使用CTE或@TableVariable,但可以使用#Temp表。创建一个temp表,将数据存储在其中(您可以将CTE结果复制到temp表)并在动态查询中使用它。这就是解决办法。