 /*利用游标生成动态sql语句,自动创建月表视图*/
select name,IDENTITY(int,1,1) as Nid into #t1 from sysobjects  where name like 'T_c_SumEmp_20%' and LEN(name)=17  ORDER BY name --把月表名称查询出来保存到临时表,在查询结果中添加自增列
go
declare t_cursor cursor for 
select name,nid from #t1
open t_cursor
declare @t_name varchar(100),@a int,@sql varchar(7000),@sql2 varchar(7000),@sql3 varchar(7000),@sql4 varchar(7000)
set @sql2='select * from '
set @sql4='if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[VIEW_SUMEmpRec]'') and OBJECTPROPERTY(id, N''IsView'') = 1) drop view [dbo].[VIEW_SUMEmpRec]'
set @sql3='create view [dbo].[VIEW_SUMEmpRec] as '
fetch next from t_cursor into @t_name,@a
while  @@Fetch_Status=0 
  begin 
    if @a=1
    begin  
      set @sql=@sql2+@t_name   
      set @sql3=@sql3+@sql
    end
    else
    begin
      set @sql=' union all '+char(13)+@sql2+@t_name   
      set @sql3=@sql3+@sql
    end
    fetch next from t_cursor into @t_name,@a
  end    
close t_cursor
deallocate t_cursor
exec(@sql4)      ---判断视图是否存在,如果存在就先删除
exec(@sql3)      ---建视图
--print @sql3      ---可以查询sql语句是否正确
drop table #t1   ---删除临时表
go

DECLARE @Date varchar(10),
        @StartDate varchar(10),
        @EndDate varchar(10)
 SELECT @StartDate= MIN(fDate) FROM T_C_SUM_OPER
 set @EndDate=CONVERT(varchar(10),GETDATE(),120)
 
 declare  cur_Day cursor for  
   SELECT CONVERT (VARCHAR (100),dateadd(day,n.number,@StartDate),23) 
   FROM master..spt_values n WHERE n.type = 'p' AND n.number <= DATEDIFF(day, @StartDate, @EndDate)
 open cur_Day    
	fetch next  from cur_Day into @Date  
	while @@fetch_status=0    
	begin  
			  EXEC UP_MakeSumEmpByday @Date
					fetch next from cur_Day into  @Date   
	end 
 close cur_Day  
 deallocate  cur_Day
 go