set @m = datediff(month,@StartTime,@endTime)set n=1 while n<=@m alter table tb add 'N'+ltrim(n) intgo
declare @n int,@m int set @m = datediff(mm,@StartTime,@endTime) set @n=1 while @n<=@m exec(alter table tb addltrim('+@n+') int') set @n=@n+1 go
少个‘ declare @n int,@m int set @m = datediff(mm,@StartTime,@endTime) set @n=1 while @n<=@m exec('alter table tb addltrim('+@n+') int') set @n=@n+1 go
declare @n int,@m int set @m = datediff(mm,@StartTime,@endTime) set @n=1 while @n<=@m exec('alter table tb add ltrim('+@n+') int') set @n=@n+1 go
ltrim 放在exec()內部也是不可行的
declare @starttime smalldatetime declare @endtime smalldatetime set @starttime = '2009-01-05' set @endtime = '2009-08-03'declare @sql varchar(8000)select @sql = isnull(@sql,'') + ',[' + convert(varchar(7),dateadd(mm,number,@starttime),120) + '] int' from master..spt_values where type = 'p' and number <= datediff(mm,@starttime,@endtime)set @sql = 'if object_id(''tempdb..##temp'') is not null drop table ##temp create table ##temp (id int ' + @sql + ')' --print @sql exec(@sql)/* select * from ##temp id 2009-01 2009-02 2009-03 2009-04 2009-05 2009-06 2009-07 2009-08 ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------(0 行受影响) */
ltrim 放在exec()內部也是不可行的 -- 錯誤的寫法 declare @n int set @n=1 exec('select '+ltrim(@n)) /* 消息 102,级别 15,状态 1,第 3 行 'ltrim' 附近有语法错误。*/ go --正確的寫法 declare @n int,@sql varchar(1000) set @n=1 set @sql='select '+ltrim(@n) exec(@sql)
alter procedure Pr_GetTab @st datetime, @et datetime as begin declare @sm tinyint, @em tinyint, @str varchar(4000) if @et<= @st begin raiserror('开始时间大于结束时间', 16, 1) return -1 end
create table #tmp (ID int primary key)
set @sm = month(@st) set @em = month(@et)
while @em>@sm begin set @str = 'alter table #tmp add m' + convert(varchar(2),@sm) + ' int null' print @str exec (@str) set @sm = @sm + 1 end select * from #tmp drop table #tmp end--exec Pr_GetTab '2010-01-01' ,'2010-07-01' alter table #tmp add m1 int null alter table #tmp add m2 int null alter table #tmp add m3 int null alter table #tmp add m4 int null alter table #tmp add m5 int null alter table #tmp add m6 int null ID m1 m2 m3 m4 m5 m6 ----------- ----------- ----------- ----------- ----------- ----------- -----------(0 行受影响)
create table tb(id int) declare @n int,@m int,@StartTime datetime,@endTime datetime,@sql varchar(1000) set @n=1 set @StartTime='2010-05-05' set @endTime = '2010-07-06' set @m = datediff(mm,@StartTime,@endTime) set @sql='' while @n<=@m begin set @sql= @sql+'alter table tb add ['+ltrim(@n)+'] int'+char(13) set @n=@n+1 end print @sql exec(@sql) go select * from tb drop table tb
while n<=@m
alter table tb add 'N'+ltrim(n) intgo
set @m = datediff(mm,@StartTime,@endTime)
set @n=1
while @n<=@m
exec(alter table tb addltrim('+@n+') int')
set @n=@n+1
go
declare @n int,@m int
set @m = datediff(mm,@StartTime,@endTime)
set @n=1
while @n<=@m
exec('alter table tb addltrim('+@n+') int')
set @n=@n+1
go
set @m = datediff(mm,@StartTime,@endTime)
set @n=1
while @n<=@m
exec('alter table tb add ltrim('+@n+') int')
set @n=@n+1
go
declare @endtime smalldatetime
set @starttime = '2009-01-05'
set @endtime = '2009-08-03'declare @sql varchar(8000)select
@sql = isnull(@sql,'') + ',[' + convert(varchar(7),dateadd(mm,number,@starttime),120) + '] int'
from master..spt_values
where type = 'p' and number <= datediff(mm,@starttime,@endtime)set @sql = 'if object_id(''tempdb..##temp'') is not null drop table ##temp create table ##temp (id int ' + @sql + ')'
--print @sql
exec(@sql)/*
select * from ##temp
id 2009-01 2009-02 2009-03 2009-04 2009-05 2009-06 2009-07 2009-08
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------(0 行受影响)
*/
-- 錯誤的寫法
declare @n int
set @n=1
exec('select '+ltrim(@n))
/*
消息 102,级别 15,状态 1,第 3 行
'ltrim' 附近有语法错误。*/
go
--正確的寫法
declare @n int,@sql varchar(1000)
set @n=1
set @sql='select '+ltrim(@n)
exec(@sql)
alter procedure Pr_GetTab
@st datetime,
@et datetime
as
begin
declare @sm tinyint,
@em tinyint,
@str varchar(4000)
if @et<= @st
begin
raiserror('开始时间大于结束时间', 16, 1)
return -1
end
create table #tmp
(ID int primary key)
set @sm = month(@st)
set @em = month(@et)
while @em>@sm
begin
set @str = 'alter table #tmp add m' + convert(varchar(2),@sm) + ' int null'
print @str
exec (@str)
set @sm = @sm + 1
end
select * from #tmp
drop table #tmp
end--exec Pr_GetTab '2010-01-01' ,'2010-07-01'
alter table #tmp add m1 int null
alter table #tmp add m2 int null
alter table #tmp add m3 int null
alter table #tmp add m4 int null
alter table #tmp add m5 int null
alter table #tmp add m6 int null
ID m1 m2 m3 m4 m5 m6
----------- ----------- ----------- ----------- ----------- ----------- -----------(0 行受影响)
declare @n int,@m int,@StartTime datetime,@endTime datetime,@sql varchar(1000)
set @n=1
set @StartTime='2010-05-05'
set @endTime = '2010-07-06'
set @m = datediff(mm,@StartTime,@endTime)
set @sql=''
while @n<=@m
begin
set @sql= @sql+'alter table tb add ['+ltrim(@n)+'] int'+char(13)
set @n=@n+1
end
print @sql
exec(@sql)
go
select * from tb
drop table tb