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='( 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 @sqlStrENDgoexec [ds_GetHisData] 'C100158','2010-11-4 00:00:00','2010-11-4 10:00:00'
