找到正解 alter procedure createdatelogas begin create table #tb_kaoqin(userid nvarchar(50),username nvarchar(50))declare @starttime datetime declare @endtime datetime declare @settime datetime declare @sql varchar(100) set @starttime='2014-01-01' set @settime='2014-01-01' set @endtime='2014-12-31' while @settime <@endtime begin --if @settime>=@endtime --return set @settime=dateadd(dd,1,@settime) set @sql='alter table #tb_kaoqin add ['+ Convert(varchar(10), @settime,120)+'] nvarchar(10)' print @sql exec (@sql) end select * from #tb_kaoqin end go exec createdatelog
刚没仔细看,你的循环是死循环,改这样:alter procedure createdatelogas set nocount on begin create table #tb_kaoqin(userid nvarchar(50),username nvarchar(50))
declare @starttime datetime declare @endtime datetime declare @settime datetime declare @sql varchar(100) set @starttime='2014-01-01' set @settime='2014-01-01' set @endtime='2014-12-31' while @starttime <@endtime begin if @settime>=@endtime return
set @settime=dateadd(dd,1,@settime) set @sql='alter table #tb_kaoqin add ['+ Convert(varchar(10), @settime,120)+'] nvarchar(10)' print @sql exec (@sql) set @starttime=dateadd(dd,1,@starttime)----添加这句话 select * from #tb_kaoqin end
as begin create table #tb_kaoqin(userid nvarchar(50),username nvarchar(50))
declare @starttime datetime declare @endtime datetime declare @settime datetime declare @sql varchar(100) set @starttime='2014-01-01' set @settime='2014-01-01' set @endtime='2014-12-31' while @starttime <@endtime begin if @settime>=@endtime return set @settime=dateadd(dd,1,@settime) set @sql='alter table #tb_kaoqin add ['+ Convert(varchar(10), @settime,120)+'] nvarchar(10)' print @sql exec (@sql) select * from #tb_kaoqin end
begin
create table #tb_kaoqin(userid nvarchar(50),username nvarchar(50))declare @starttime datetime
declare @endtime datetime
declare @settime datetime
declare @sql varchar(100)
set @starttime='2014-01-01'
set @settime='2014-01-01'
set @endtime='2014-12-31'
while @settime <@endtime
begin
--if @settime>=@endtime
--return
set @settime=dateadd(dd,1,@settime)
set @sql='alter table #tb_kaoqin add ['+ Convert(varchar(10), @settime,120)+'] nvarchar(10)'
print @sql
exec (@sql)
select * from #tb_kaoqinendend
go
exec createdatelog
alter table #tb_kaoqin add [2014-12-29] nvarchar(10)
警告: 已经创建表 '#tb_kaoqin',但是它的最大行大小超过了允许的最大字节数 8060。如果得到的行超过此大小限制,则对此表的 INSERT 或 UPDATE 操作将失败。
alter procedure createdatelogas
begin
create table #tb_kaoqin(userid nvarchar(50),username nvarchar(50))declare @starttime datetime
declare @endtime datetime
declare @settime datetime
declare @sql varchar(100)
set @starttime='2014-01-01'
set @settime='2014-01-01'
set @endtime='2014-12-31'
while @settime <@endtime
begin
--if @settime>=@endtime
--return
set @settime=dateadd(dd,1,@settime)
set @sql='alter table #tb_kaoqin add ['+ Convert(varchar(10), @settime,120)+'] nvarchar(10)'
print @sql
exec (@sql)
end
select * from #tb_kaoqin
end
go
exec createdatelog
set nocount on
begin
create table #tb_kaoqin(userid nvarchar(50),username nvarchar(50))
declare @starttime datetime
declare @endtime datetime
declare @settime datetime
declare @sql varchar(100)
set @starttime='2014-01-01'
set @settime='2014-01-01'
set @endtime='2014-12-31' while @starttime <@endtime
begin
if @settime>=@endtime
return
set @settime=dateadd(dd,1,@settime)
set @sql='alter table #tb_kaoqin add ['+ Convert(varchar(10), @settime,120)+'] nvarchar(10)'
print @sql
exec (@sql)
set @starttime=dateadd(dd,1,@starttime)----添加这句话
select * from #tb_kaoqin
end
end
go
exec createdatelog
as
begin
create table #tb_kaoqin(userid nvarchar(50),username nvarchar(50))
declare @starttime datetime
declare @endtime datetime
declare @settime datetime
declare @sql varchar(100)
set @starttime='2014-01-01'
set @settime='2014-01-01'
set @endtime='2014-12-31'
while @starttime <@endtime
begin
if @settime>=@endtime
return
set @settime=dateadd(dd,1,@settime)
set @sql='alter table #tb_kaoqin add ['+ Convert(varchar(10), @settime,120)+'] nvarchar(10)'
print @sql
exec (@sql)
select * from #tb_kaoqin
end
end
go
exec createdatelog