with as表达式 和 CURSOR游标解决方案

set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[ds_GetHisData]    @OrgId nvarchar(30),    @FromTime DateTime,    @EndTime DateTimeASBEGIN    SET NOCOUNT ON;    Declare @jlyid nvarchar(20) ;    Declare @sqlStr nvarchar(2000);    Declare @tablenames nvarchar(1024);    --通过递归取得所有的组织编号    WITH userorgs(op_upkey,op_name,op_key,op_rank,orglevel)    AS    (        SELECT op_upkey,op_name,op_key,op_rank ,0 AS orglevel FROM organize_info        WHERE op_key = @OrgId        UNION ALL        SELECT l.op_upkey,l.op_name,l.op_key,l.op_rank,orglevel+1 FROM organize_info l             INNER JOIN userorgs p ON l.op_upkey=p.op_key    )    --with as 后面必须跟着使用表达式的语句，我想把userorgs用在游标cur_JLYList里    select * from userorgs        --在这个游标里用userorgs无效？？？？？    DECLARE cur_JLYList CURSOR FAST_FORWARD FOR    select G_JLYID    from JLY_Info    where g_opkey in (SELECT op_key FROM userorgs);    OPEN cur_JLYList;    FETCH NEXT FROM cur_JLYList INTO @JLYId        ---日期分表处理        set @tablenames='( [email protected]+'.dbo.his'+Datepart(yyyy,@FromTime)+Datepart(mm,@FromTime)        while Datepart(yyyy,@FromTime)!=Datepart(yyyy,@EndTime) or Datepart(mm,@FromTime)!=Datepart(mm,@EndTime)        begin            set @FromTime=Dateadd(m,1,@FromTime)            set @[email protected]+' union [email protected]+'.dbo.his'+Datepart(yyyy,@FromTime)+Datepart(mm,@FromTime)        end        set @[email protected]+') as [email protected]                set @sqlStr = ' select * from [email protected]    WHILE @@FETCH_STATUS = 0 --返回被 FETCH  语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。    begin        --开始循环游标变量        FETCH NEXT FROM cur_JLYList INTO @JLYId        --执行sql操作        ---日期分表处理        set @tablenames='( [email protected]+'.dbo.his'+Datepart(yyyy,@FromTime)+Datepart(mm,@FromTime)        while Datepart(yyyy,@FromTime)!=Datepart(yyyy,@EndTime) or Datepart(mm,@FromTime)!=Datepart(mm,@EndTime)        begin            set @FromTime=Dateadd(m,1,@FromTime)            set @[email protected]+' union [email protected]+'.dbo.his'+Datepart(yyyy,@FromTime)+Datepart(mm,@FromTime)        end        set @[email protected]+') as [email protected]                set @sqlStr = @sqlStr + ' union select * from [email protected]    end    print @sqlStrENDgoexec [ds_GetHisData] 'C100158','2010-11-4 00:00:00','2010-11-4 10:00:00'