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

来源：互联网发布：网络小手分享论坛社区编辑：程序博客网时间：2024/06/05 23:00

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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- 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 datetimeASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;declare @sql varchar(max),    @num int,@i intselect  @num=datediff(DAY, @start, @end)--select @numselect number, [date]=convert(char(8), dateadd(DAY, number, @start), 112) into #tempfrom(select top(datediff(DAY, @start, @end)+1) numberfrom master..spt_valueswhere 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=0while  @i<=@num BEGINdeclare @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 ENDset @sql+=' order by dtime' drop table #temp-- print @sqlexec (@sql)END

注：直接创建此存储过程时，需要将代码中的“ALTER ”改为“CREATE”

0 0