DECLARE @a DATETIME ='2012-04-05',@b DATETIME ='2012-9-12' DECLARE @sql VARCHAR(MAX),@i INT ,@t DATETIME SET @i=0 SET @t=DATEADD(mm,DATEDIFF(mm,0,@a),0) --SELECT @t WHILE @i<=DATEDIFF(mm,@a,@b) BEGIN SELECT @sql=ISNULL(@sql+CHAR(10)+'union all'+CHAR(10),'')+'select * from tb'+CONVERT(VARCHAR(8),dateadd(mm,@i,@t),112) SET @i=@i+1 END --PRINT @sql /* select * from tb20120401 union all select * from tb20120501 union all select * from tb20120601 union all select * from tb20120701 union all select * from tb20120801 union all select * from tb20120901* */ EXEC (@sql)
例如
declare @t varchar ='tb201205'
exec('select * from '+@t)
DECLARE @sql VARCHAR(MAX),@i INT ,@t DATETIME
SET @i=0
SET @t=DATEADD(mm,DATEDIFF(mm,0,@a),0)
--SELECT @t
WHILE @i<=DATEDIFF(mm,@a,@b)
BEGIN
SELECT @sql=ISNULL(@sql+CHAR(10)+'union all'+CHAR(10),'')+'select * from tb'+CONVERT(VARCHAR(8),dateadd(mm,@i,@t),112)
SET @i=@i+1
END
--PRINT @sql
/*
select * from tb20120401
union all
select * from tb20120501
union all
select * from tb20120601
union all
select * from tb20120701
union all
select * from tb20120801
union all
select * from tb20120901* */
EXEC (@sql)