DECLARE @fdate smalldatetime DECLARE @edate smalldatetime DECLARE @i int DECLARE @gg char(800) DECLARE @sql varchar(2000)set @fdate ='2005-09-01' set @edate='2005-09-01' set @i= datepart(day,@edate-@fdate) select onu.onu into #tmp_dateonu from onu while @i > 0
Begin
set @fdate=@fdate set @gg=convert(char(800),datepart(day,@fdate)) exec ('alter table #tmp_dateonu add '+@gg+' int') set @sql=' update t set ['+datepart(day,@fdate)+']=A.gg from #tmp_dateonu t join (select onu,count(*) as gg from yewsl where 受理时间>='''+@fdate+''' and 受理时间<='''+@fdate+''' group by onu) A on A.onu=t.onu ' exec(@sql) set @fdate=dateadd(day,1,@fdate) end
viva, 还是不可以啊,出错了。 服务器: 消息 170,级别 15,状态 1,行 1 第 1 行: '1' 附近有语法错误。 服务器: 消息 245,级别 16,状态 1,行 27 将 varchar 值 ' update t set [' 转换为数据类型为 int 的列时发生语法错误。
DECLARE @edate smalldatetime
DECLARE @i int
DECLARE @gg char(800)
DECLARE @sql varchar(2000)set @fdate ='2005-09-01'
set @edate='2005-09-01'
set @i= datepart(day,@edate-@fdate) select onu.onu into #tmp_dateonu from onu while @i > 0
Begin
set @fdate=@fdate set @gg=convert(char(800),datepart(day,@fdate)) exec ('alter table #tmp_dateonu add '+@gg+' int') set @sql='
update t
set ['+datepart(day,@fdate)+']=A.gg
from #tmp_dateonu t
join (select onu,count(*) as gg from yewsl where 受理时间>='''+@fdate+''' and 受理时间<='''+@fdate+''' group by onu) A on A.onu=t.onu
'
exec(@sql)
set @fdate=dateadd(day,1,@fdate)
end
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: '1' 附近有语法错误。
服务器: 消息 245,级别 16,状态 1,行 27
将 varchar 值 '
update t
set [' 转换为数据类型为 int 的列时发生语法错误。
create table #tmp_dateonu (tt int)
declare @gg varchar(800)
declare @sql varchar(800)
set @gg=convert(varchar(800),datepart(day,2))--假设@fdate=2
--print @gg
--print 'alter table #tmp_dateonu add [' + convert(varchar(800),@gg) + '] int'
set @sql = 'alter table #tmp_dateonu add [' + convert(varchar(800),@gg) + '] int'
exec (@sql)select * from tmp_dateonu
/*
tt 3
----------- ----------- (0 row(s) affected)
*/
已经加进去了