declare @sql varchar(6000),@BeginDay int,@EndDay int

select @BeginDay=1,@EndDay=6

select @sql='with t as ('+char(10)

while(@BeginDay<=@EndDay)
begin
 select @sql=@sql+'select * from tb_'+cast(@BeginDay as varchar)+char(10)+'union all '+char(10)
 select @BeginDay=@BeginDay+1
end

select @sql=left(@sql,len(@sql)-12)+char(10)+') select * from t;'

-- 执行
exec(@sql)


-- 打印SQL
print @sql

--> 结果
with t as (
select * from tb_1
union all 
select * from tb_2
union all 
select * from tb_3
union all 
select * from tb_4
union all 
select * from tb_5
union all 
select * from tb_6
) select * from t;

------解决方案--------------------
SQL code

declare @num int 
set @num=1
declare @sql varchar(max)
set @sql=''
while @num<=101
begin
declare @str varchar(max)
set @str='select * from tbl'+cast(@num as varchar)+' union all'
set @num+@num+1
set @sql=@sql+@str
end
set @sql=left(@sql,len(@sql)-9)
exec(@sql)

这样你可以通过控制@num来实现查询的日期