drop table ##a
drop table ##bdeclare @i int, @rq1 char(10),@rq2 char(10),@s varchar(8000)
set @rq1 = '2008-05-01'
set @rq2 = '2008-08-01'
set @s = 'select convert(char(7),dateadd(month,id,'''+@rq1 +'''),121) as xxx into ##a from
(select top '+cast(datediff(month,@rq1,@rq2)+1 as char(3)) + '(select sum(1) from sysobjects where name <= a.name) - 1 as id from sysobjects a) bb'
exec (@s)
select IDENTITY(int, 1,1) as id, 'select * from PUB..log_' + substring(xxx, 1, 4) + '_' + substring(xxx, 6, 2) + ' union all ' as sSqlString into ##b from ##a
update ##b set sSqlString = substring(sSqlString, 1, 30) where id = (select max(id) from ##b) select * from ##b
set @s = '' select @i = max(id) from ##b while @i > 0
begin
select @s = @s + sSqlString from ##b where id = @i
set @i = @i - 1
end exec(@s)生成的临时表#b的内容如下:
id sSqlString
1 select * from PUB..log_2008_05 union all
2 select * from PUB..log_2008_06 union all
3 select * from PUB..log_2008_07 union all
4 select * from PUB..log_2008_08我是想将sSqlString字段中的每条记录串起来,得到select * from PUB..log_2008_05 union all select * from PUB..log_2008_06 union all select * from PUB..log_2008_07 union all select * from PUB..log_2008_08这样一条语句,然后放在exec(@s)中执行,但为何无法生成最后的那个@s呢。奇怪。
--> 改成下面的(1. 语句之间需要空格分开 2. 你是倒过来拼语句的, 所以拼字符串的写法也要倒过来)
select @s = sSqlString + ' ' + @s from ##b where id = @i
@i > 0 改为@i>=0或者:
declare @s varchar(8000),@count int,@i int
select @s = '',@i=1
set @count=(select max(id) from tb) while @i <=@count
begin
select @s = @s +sSqlString+' ' from tb where id = @i
set @i = @i +1
endprint @sselect * from PUB..log_2008_08 select * from PUB..log_2008_07 union all select * from PUB..log_2008_06 union all select * from PUB..log_2008_05 union all