临时视图的用法

根据日期参数，创建临时视图
用途：多个用时间命名的表的数据查询
ALTER PROCEDURE dbo.up_CreatTempView
    (
         @Days varchar(4000),   --日期（不含时间）
         @start_time varchar(5),  --起始时间 24:00
         @end_time varchar(5),  --结束时间   24:00
         @query varchar(500),
         @fileds varchar(500), --字段
         @ViewName varchar(50)=null output
    )
AS
begin
DECLARE @SQL varchar(4000)
DECLARE @tmpView varchar(4000)
SET @tmpView=''
---分离字符串@Days
--declare @Days varchar(4000)
declare @T varchar(50)  --数据表
declare @tDay varchar(20)
declare @tmpDate DateTime
--set @Days='2006-01-12|2006-02-13|2006-03-15|2006-11-11|2003-01-01|'
--set @tmpDay=''
declare @i int
set @i=0
while @i<len(@Days)
begin
  set @i=@i+1
  if SUBSTRING(@Days,@i,1)='|'
     begin
       set @tDay=left(@Days,@i-1)  --已经分离出的日期
       --格式化 @tDay日期 2006-01-02 to 20060102
       set @tmpDate=cast(@tDay AS DateTime)      
       -----组合表名       
        SET @T=DATEPART(YYYY,@tmpDate) 
        if DATEPART(MM,@tmpDate)<10
            set @T=@T+'0'+cast(DATEPART(MM,@tmpDate) as char(1))
          else
            set @T=@T+CAST(DATEPART(MM,@tmpDate) AS CHAR(2))
        if DATEPART(DD,@tmpDate)<10
            set @T=@T+'0'+cast(DATEPART(DD,@tmpDate) as char(1))
        else
            set @T=@T+CAST(DATEPART(DD,@tmpDate) AS CHAR(2))
        SET @T='FACT_'+@T
     ---------
      
       set @Days=SUBSTRING(@Days,@i+1,len(@Days))--剩下的日期字符串
       set @i=0
       --检测@tmpDay 这一天，是否存在数据
       --set @tmpDay='FACT_'+
      -- print @tmpDay
        if exists (select * from sysobjects where id = object_id(@T) and OBJECTPROPERTY(id, 'IsUserTable') = 1)
           begin
             -- print @tmpDay
              --存在数据
              if len(@tmpView)>0
                 set @tmpView=@tmpView+' union all select '+@fileds+' from '+@T+' where start_time>='''+@tDay+' '+@start_time+''' and end_time<='''+@tDay+' '+@end_time+''' and '+@query
              else
                 set @tmpView='select '+@fileds+' from '+@T+' where start_time>='''+@tDay+' '+@start_time+''' and end_time<='''+@tDay+' '+@end_time+''' and '+@query           
           end
       --检测//end
     end 
end
--print @Days
--print @tmpView
---分离字符串//end
if len(@tmpView)>0
begin
  --创建一个零时试图
  --declare @ViewName varchar(20) --临时视图名
  SET @ViewName='tmpV'+cast(DATEPART(YYYY,getDate()) as varchar(4))+
  cast(DATEPART(MM,getDate()) as varchar(2))+
  cast(DATEPART(DD,getDate()) as varchar(2))+
  cast(DATEPART(HH,getDate()) as varchar(2))+
  cast(DATEPART(SS,getDate()) as varchar(2))+
  cast(DATEPART(MS,getDate()) as varchar(4))
  set @tmpView='CREATE  VIEW '+@ViewName+ ' as '+@tmpView
  --print @tmpView
  exec(@tmpView)
  --临时视图创建完毕
  return 1
end
else
  begin
  set @ViewName='' --没有数据,无法创建视图
  return -1
  end
--exec('select '+@fileds+' from '+@vName)
--exec('drop view '+@vName)
end