SQL SERVER 2008 存储过程 --跨表查询(例)

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”