Dynamic SQL ADD Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic SQL ADD Column

Author

Topic

harrisw48
Starting Member

34 Posts

Posted - 2006-11-10 : 10:44:19

I have the code below.

declare @num int
declare @sql as nvarchar(4000)

set @sql = 'add [column name] varchar(100)'

alter table table name
exec (@sql)

When I run it I get a syntax error
WHY?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-10 : 10:54:54

Put the entire Alter table statement inside EXEC:

set @sql = 'Alter table tablename add col varchar(100)'
exec(@sql)

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-10 : 11:02:12

Why do you want to add column dynamically?
http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

harrisw48
Starting Member

34 Posts

Posted - 2006-11-12 : 09:59:06

OK

That worked OK but when I try and further change the code to have a dynamic varable in the field name I get an error.

declare @num int
declare @sql as nvarchar(4000)

set @num = 5
set @sql = 'alter table table name
add [column name ' + @num + '] varchar(100)'
exec (@sql)

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-12 : 10:04:27

[code]
declare @num int
declare @sql as nvarchar(4000)

set @num = 5
set @sql = 'alter table Table_Name
add [column name ' + convert(Varchar(4),@num )+ '] varchar(100)'
print @sql
[/code]

Chirag

http://chirikworld.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-12 : 20:29:15

quote:
Originally posted by harrisw48

OK

That worked OK but when I try and further change the code to have a dynamic varable in the field name I get an error.

declare @num int
declare @sql as nvarchar(4000)

set @num = 5
set @sql = 'alter table table name
add [column name ' + @num + '] varchar(100)'
exec (@sql)

Read the link I posted

Madhivanan

Failing to plan is Planning to fail

harrisw48
Starting Member

34 Posts

Posted - 2006-11-14 : 03:09:03

Thanks, that works great

How do I go about adding further fields in the Alter Table T-SQL

I currently have

declare @NumContacts int
declare @Counter int
declare @sql nvarchar(4000)

set @Numcontacts = 5
set @Counter = 0

while @counter < @numcontacts
begin
set @Counter = @Counter + 1
set @sql = 'alter table dbo.tblmain add [contact urn' + convert(varchar(2),@Counter) + '] varchar(100)'
print @sql
end

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-14 : 03:28:00

"How do I go about adding further fields in the Alter Table T-SQL"
separate it by comma


alter table dbo.tblmain add column1 datatype, column2 datatype . . .

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources