SQL Server 2008中使用CTE进行动态查询

Question

我正在尝试编写使用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表或表变量，它会起作用吗。

- josephj1989

---

你不能在 CTE 中引用 CTE。 - websch01ar

为什么需要 select COUNT(*) from months？还有为什么需要一个 dynamic 查询？ - van

如果我不加上select count(*)，它会报错“CTE未使用”。我正在尝试创建一个沿Y轴具有月份的透视查询。月份数量是可变的，因此我将不得不创建类似于pivot(sum(fld) for col in(['jan-10'],['feb-10']...)的透视表。 - josephj1989

我认为你可能忽略了 SQL Server 发送的更大信息：不要在 T-SQL 中进行动态交叉表。该语言并不适用于此，您必须跳过许多障碍才能使其正常工作。使用报告工具或在中间层组件中完成它。 - Thomas

Answer 1

您的动态SQL不能引用months。公共表表达式（CTE）的作用域是单个语句：

with cte as (cte definiton) select from cte;

如果您想重复使用CTE的结果或定义，您必须每次想要使用它时重新定义CTE（例如在@DynSql中），或将其结果实现为表@variable并重复使用表@variable。

- Remus Rusanu

---

我尝试了使用表变量，但似乎在动态SQL中无法使用表变量。 - josephj1989

动态SQL在不同的作用域中执行。基本上，它是一个函数调用。它无法引用您当前上下文的变量。您必须将要使用的任何变量从当前范围/上下文作为参数传递，使用exec sp_executesql @DynSql,'@parameterName类型'，@localTVP。 - Remus Rusanu

请参考 http://msdn.microsoft.com/en-us/library/bb510489.aspx 中的示例，了解如何将本地表 @variable 传递给一个存储过程。 - Remus Rusanu

我使用CTE创建了一个表变量，并尝试将其传递给sp_execsql，但它不接受表变量。 - josephj1989

Answer 2

with关键字不会声明一个可以在后续查询中引用的对象，它是select查询的一部分。你的动态SQL试图引用一个不存在的对象months。在定义动态查询字符串时，请将CTE包含在内。

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) 

- Shannon Severance

---

我已经尝试过了。我需要使用数据透视表来显示跨页面的月度数据。我正在尝试创建一个带有月份的数据透视表查询，其中月份沿Y轴排列。月份数量是可变的，因此我将不得不创建类似于pivot(sum(fld) for col in(['jan-10'],['feb-10']...)的数据透视表。因此，我必须将月份列表作为动态查询的一部分进行创建。但是，数据透视表查询与月份CTE交叉连接。因此，我需要在动态SQL之外和之内定义月份CTE。我尝试将月份插入到表变量中并将其传递给sp_execsql，但它不接受表变量。 - josephj1989

是的，如果没有视图，您需要在动态SQL内部创建CTE。我不明白问题出在哪里。也许发布更多的问题和错误信息会有所帮助。 - Shannon Severance

Answer 3

好的，我已经让它工作了，但我不理解这个的范围...

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)

- websch01ar

---

不，我的问题不是选择cte中的count(*)，而是在动态SQL中使用CTE。 - josephj1989

现在我看到你正在尝试使用Pivot()。根据你提供的数据，你将得到一个参考点。你想要与什么进行比较呢？如果能提供更多信息，我可以使用cte。 - websch01ar

注释部分是关于轴心的，目前我无法使其工作。 - websch01ar

Answer 4

你不能在动态 SQL 中使用 CTE 或 @TableVariable，但是你可以使用 #Temp 表来实现。创建一个临时表，将数据存储在其中（你可以将 CTE 的结果复制到临时表中），然后在动态查询中使用它。这就是解决方案。

- Abdul Rasheed