34,576
社区成员
发帖
与我相关
我的任务
分享
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
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 @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 行受影响)
*/
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
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
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