临时视图的用法

根据日期参数，创建临时视图
用途：多个用时间命名的表的数据查询
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