日期:2014-05-18  浏览次数:20693 次

MSSQL循环添加列
SQL code

--这个表中放入ID,与我将要添加的列名
select identity (int,1,1)as ID  ,HW_PART_NO into HW_Row from ['WINDOWS 7 32$'] group by HW_PART_NO

declare @row nvarchar(255)
declare @i int
select @i=COUNT(*)from SUB_Row
while(@i>0)
begin
select @row=SUB_PH_NUMBER_AND_DESCRIPTION from SUB_Row where SUBID=@i
set @i=@i-1
alter table SRV_SUB add @row nvarchar(255)
end

报错:Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '@row'.




语句我个人认为是没有问题的,因为我昨天也同样适用这个语句添加了新列.
估计是不是数据类型有问题?或者说是声明的变量使用有问题

------解决方案--------------------
SQL code
--这个表中放入ID,与我将要添加的列名
select identity (int,1,1)as ID  ,HW_PART_NO into HW_Row from ['WINDOWS 7 32$'] group by HW_PART_NO

declare @row nvarchar(255)
declare @i int
select @i=COUNT(*)from SUB_Row
while(@i>0)
begin
select @row=SUB_PH_NUMBER_AND_DESCRIPTION from SUB_Row where SUBID=@i
set @i=@i-1
EXEC ('alter table SRV_SUB add '+@ROW+' nvarchar(255)')
end

------解决方案--------------------
SQL code

select identity (int,1,1)as ID  ,HW_PART_NO into HW_Row from ['WINDOWS 7 32$'] group by HW_PART_NO

declare @row nvarchar(255)
declare @i int
select @i=COUNT(*)from SUB_Row
while(@i>0)
begin
select @row=SUB_PH_NUMBER_AND_DESCRIPTION from SUB_Row where SUBID=@i
set @i=@i-1
exec('alter table SRV_SUB add '+@row+' nvarchar(255)')
end

------解决方案--------------------
try this,
SQL code

alter table SRV_SUB add @row nvarchar(255)

--> 改为

exec('alter table SRV_SUB add ['+@row+'] nvarchar(255) ')

------解决方案--------------------
SQL code
EXEC ('alter table SRV_SUB add '+@ROW+' nvarchar(255)')

------解决方案--------------------
DDL语句不支持带变量.