DECLARE @temp varchar(20)
set @temp=cast( DATEPART("year",GETDATE()) as varchar(20))
alter table Basic add @temp int null default 0 with values
这样执行结果 :
'@temp' 附近有语法错误。
set @temp=cast( DATEPART("year",GETDATE()) as varchar(20))
alter table Basic add @temp int null default 0 with values
这样执行结果 :
'@temp' 附近有语法错误。
这样的动态,还不如直接更新
set @temp=cast( DATEPART("year",GETDATE()) as varchar(20))
--alter table BasicName add @temp int null default 0 with valuesDECLARE @sql varchar(200)
set @sql='alter table BasicName add' +@temp+ 'int null default 0 with values'
exec(@sql)在关键字 'null' 附近有语法错误。
set @temp=cast( DATEPART(year,GETDATE()) as varchar(20))
--alter table BasicName add @temp int null default 0 with valuesDECLARE @sql varchar(200)
set @sql='alter table BasicName add [' +@temp+ '] int null default 0 with values'
exec(@sql)
set @temp=cast( DATEPART("year",GETDATE()) as varchar(20))+cast( DATEPART("month",GETDATE()) as varchar(20))
--alter table BasicName add @temp int null default 0 with valuesDECLARE @sql varchar(200)
set @sql='alter table BasicName add ' +'aaa'+@temp+ ' int null default 0 with values'
exec(@sql)但是我列名不能用纯数字...
DECLARE @temp varchar(20)
set @temp=cast( DATEPART(year,GETDATE()) as varchar(20))
--alter table BasicName add @temp int null default 0 with valuesDECLARE @sql varchar(200)
set @sql='alter table BasicName add' +@temp+ 'int null default 0 with values'
exec(@sql)
set @temp=cast( DATEPART(year,GETDATE()) as varchar(20))
EXEC('alter table Basic add '+@temp+' int null default 0 with values ')
set @temp=cast( DATEPART(year,GETDATE()) as varchar(20))
EXEC('alter table Basic add ['+@temp+'] int null default 0 with values ')数字型和关键字要加[]
DECLARE @year varchar(20)
DECLARE @month varchar(20)
set @year=cast( DATEPART("year",GETDATE()) as varchar(20))
set @month=cast( DATEPART("month",GETDATE()) as varchar(20))
if @month<10
begin
set @month='0'+@month
end
set @temp=@year+@monthDECLARE @sql varchar(200)
set @sql='alter table BasicName add '+'money'+@temp+ ' int null default 0 with values'
exec(@sql)
create table ta(name varchar(12))insert ta select
'a'union all select
'd'union all select
'x'union all select
'b'--select * from ta
DECLARE @temp varchar(5000) ,@tt varchar(20)
set @tt=cast( DATEPART("year",GETDATE()) as varchar(20))
set @temp='alter table ta add ['+@tt +'] int null default 0 with values'
exec(@temp)select * from taname 2009
------------ -----------
a 0
d 0
x 0
b 0(所影响的行数为 4 行)
DECLARE @sql varchar(200)
set @sql='alter table BasicName add '+'money'+convert(varchar(6),getdate(),112)+ ' int null default 0 with values'
exec(@sql)这样就行了.
DECLARE @temp varchar(20)
set @temp=cast( DATEPART(year,GETDATE()) as varchar(20))
exec ( 'alter table Basic add [' + @temp +'] int null default 0 with values')