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

使用CTE的sqlserver2008动态查询

  •  3
  • josephj1989  · 技术社区  · 15 年前

    我正在尝试编写一个使用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 回复  |  直到 15 年前
        1
  •  3
  •   Remus Rusanu    15 年前

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

    with cte as (cte definiton) select from cte;
    

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

        2
  •  3
  •   Shannon Severance    15 年前

    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) 
    
        3
  •  0
  •   websch01ar    15 年前

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

    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)
    
        4
  •  0
  •   Abdul Rasheed    9 年前

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