SQL SERVER数据库项目中用到了对自动生成的表的查询。
表的命名规律如 T_His20141021 即 T_Hisyyyymmdd
由于这些表都是通过模板T_History表生成的,故自动生成的这些表的字段类型和长度都与表T_History一样。
USE [mydatabase]
GO
/****** Object: StoredProcedure [dbo].[P_DataSearch] Script Date: 10/21/2014 15:27:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <XXX>
-- Create date: <2014-mm-dd>
-- Description: <查询历史数据信息>
-- =============================================
ALTER PROCEDURE [dbo].[P_GetHistoryDataTable]
-- Add the parameters for the stored procedure here
@inid varchar(10),
@start datetime,
@end datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @sql varchar(max),
@num int,
@i int
select @num=datediff(DAY, @start, @end)
--select @num
select number, [date]=convert(char(8), dateadd(DAY, number, @start), 112) into #temp
from
(
select top(datediff(DAY, @start, @end)+1) number
from master..spt_values
where type = 'p'
) T
set @sql='select ins.insname,his.dtime,his.dvalue from T_Intimes ins,T_History his where ins.inid=his.isid and ins.inid='''+@inid+''' '
set @i=0
while @i<=@num
BEGIN
declare @temday varchar(20)
begin
select @temday=[date] from #temp where number=@i
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE ID = object_id(N'T_His'+@temday) AND OBJECTPROPERTY(ID, 'IsTable') = 1)
begin
set @sql+=' union select ins.insname,his.dtime,his.dvalue from T_Intimes ins,T_His'+@temday+' his where ins.inid=his.isid and ins.inid='''+@inid+''' '
end
end
set @i=@i+1
END
set @sql+=' order by dtime'
drop table #temp
-- print @sql
exec (@sql)
END
注:直接创建此存储过程时,需要将代码中的“ALTER ”改为“CREATE”