set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[ds_GetHisData]
    @OrgId nvarchar(30),
    @FromTime DateTime,
    @EndTime DateTime
AS
BEGIN
    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='( HisData'+@JLYId+'.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 @tablenames=@tablenames+' union HisData'+@JLYId+'.dbo.his'+Datepart(yyyy,@FromTime)+Datepart(mm,@FromTime)
        end
        set @tablenames=@tablenames+') as hisdata'+@jlyid
        
        set @sqlStr = ' select * from '+@tablenames
    WHILE @@FETCH_STATUS = 0 --返回被 FETCH  语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。
    begin
        --开始循环游标变量
        FETCH NEXT FROM cur_JLYList INTO @JLYId
        --执行sql操作
        ---日期分表处理
        set @tablenames='( HisData'+@JLYId+'.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 @tablenames=@tablenames+' union HisData'+@JLYId+'.dbo.his'+Datepart(yyyy,@FromTime)+Datepart(mm,@FromTime)
        end
        set @tablenames=@tablenames+') as hisdata'+@jlyid
        
        set @sqlStr = @sqlStr + ' union select * from '+@tablenames
    end
    print @sqlStr
END

go
exec [ds_GetHisData] 'C100158','2010-11-4 00:00:00','2010-11-4 10:00:00'