How can I add column dynamic - 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

General SQL Server Forums

New to SQL Server Programming

How can I add column dynamic

Author

Topic

thomson
Starting Member

9 Posts

Posted - 2009-03-05 : 16:21:10

I want to add column dynamic. please see attached code. How can I add column by variable?

AL TER TABLE #temp ADD @t char(3)

thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-05 : 16:43:56

You would have to use dynamic sql for this. ie:


create table #temp (i int)

select * from #temp

declare @sql varchar(200)
       ,@newcol varchar(30)

set @newcol = 'newCol varchar(10)'
set @sql = 'alter table #temp add ' + @newcol

exec (@sql)

select * from #temp
drop table #temp

OUTPUT:
i
-----------


i           newCol
----------- ----------

Be One with the Optimizer
TG

thomson
Starting Member

9 Posts

Posted - 2009-03-05 : 17:29:14

[quote]Originally posted by TG

I change the code below,it is not work. why?
[code]
set @date='2008-02-03'

set @newcol = @date+' varchar(30)'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-05 : 19:13:30

>>It's not ok
why? did you get an error? what is the problem, specifically?

It's probably because you need square brackets around a column that starts with a number or contains spaces or is a key word or otherwise violates sql object naming conventions.

set @date = '[2008-02-03]'

Be One with the Optimizer
TG

thomson
Starting Member

9 Posts

Posted - 2009-03-06 : 08:56:56

Thanks a lot

thomson
Starting Member

9 Posts

Posted - 2009-03-06 : 14:27:15

One more question.How could I write code

set @sql='update #temp set '+'absentee='+'A'
exec(@sql)

It doesn't work.How to deal with 'A' issue?
thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-06 : 14:32:54

set @sql='update #temp set '+'absentee='+'''A'''
exec(@sql)