declare @begin_date DATETIME,@end_time DATETIME ,@col VARCHAR(1000)
SELECT  @begin_date='2012-08-01',@end_time='2012-08-10',@col='id,name,time'

DECLARE @sql VARCHAR(max)
WHILE @begin_date<=@end_time
BEGIN 
SELECT @sql=ISNULL(@sql+CHAR(10)+'union all'+CHAR(10),'')+'select '+@col+' from tb_'+CONVERT(VARCHAR(8),@begin_date,112)
SET @begin_date=@begin_date+1
END 
PRINT @sql
EXEC(@sql)

------解决方案--------------------
用动态的拼接语句

下面给你写个小例子
SQL code

declare @begin_date DATETIME,@end_time DATETIME ,@col VARCHAR(1000)
SELECT  @begin_date='2012-08-01',@end_time='2012-08-10',@col='id,name,time'

DECLARE @sql VARCHAR(max)
WHILE @begin_date<=@end_time
BEGIN 
SELECT @sql=ISNULL(@sql+CHAR(10)+'union all'+CHAR(10),'')+'select '+@col+' from tb_'+CONVERT(VARCHAR(8),@begin_date,112)
SET @begin_date=@begin_date+1
END 
PRINT @sql
EXEC(@sql)