alter table with variable colum

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 2005 Forums

Transact-SQL (2005)

alter table with variable colum

Author

Topic

avijit_mca
Posting Yak Master

109 Posts

Posted - 2009-01-05 : 07:47:32

create table junk (id varchar(20),name varchar(50))

ALTER TABLE junk
ADD cola nvarchar(299) NULL

Is it possible to create column with variable.

My aim :
declare @i as int
declare @x as varchar(3)
set @x='aa'+@i
set @i=1
while @i<=10
begin

ALTER TABLE junk
ADD @x nvarchar(299) NULL -- Error
end

Actually i want to create column run time. bcoz no of column are not fixed .. its depend on no of days in months.
so if no of months is 30 then i need 30 + 2 (fixed column)column.

if u have any good idea pls share with me.
Regards,
Avijit

Regards,
avijit

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-05 : 07:49:40

use dynamic sql.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-05 : 07:56:15

On a different note, I see some problems there in your code. You are missing the increment to @i and change to @x.

I'd use something like this.

declare @i as varchar(3)
declare @x as varchar(3)
set @x='aa'+@i
set @i=1
while @i<=2
begin 
set @x='aa'+@i
exec ('ALTER TABLE junk ADD '+@x+' nvarchar(299) ')
set @i=@i+1
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 08:57:31

quote:
Originally posted by avijit_mca

create table junk (id varchar(20),name varchar(50))

ALTER TABLE junk
ADD cola nvarchar(299) NULL

Is it possible to create column with variable.

My aim :
declare @i as int
declare @x as varchar(3)
set @x='aa'+@i
set @i=1
while @i<=10
begin

ALTER TABLE junk
ADD @x nvarchar(299) NULL -- Error
end

Actually i want to create column run time. bcoz no of column are not fixed .. its depend on no of days in months.
so if no of months is 30 then i need 30 + 2 (fixed column)column.

if u have any good idea pls share with me.
Regards,
Avijit

Regards,
avijit

why do you want to generate columns like this? if your reqmnt is to get monthwise totals of something, cant you use pivot oir similar crosstabbing options?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-06 : 07:37:41

quote:
Originally posted by visakh16

quote:
Originally posted by avijit_mca

create table junk (id varchar(20),name varchar(50))

ALTER TABLE junk
ADD cola nvarchar(299) NULL

Is it possible to create column with variable.

My aim :
declare @i as int
declare @x as varchar(3)
set @x='aa'+@i
set @i=1
while @i<=10
begin

ALTER TABLE junk
ADD @x nvarchar(299) NULL -- Error
end

Actually i want to create column run time. bcoz no of column are not fixed .. its depend on no of days in months.
so if no of months is 30 then i need 30 + 2 (fixed column)column.

if u have any good idea pls share with me.
Regards,
Avijit

Regards,
avijit

why do you want to generate columns like this? if your reqmnt is to get monthwise totals of something, cant you use pivot oir similar crosstabbing options?

Yes. It seems OP needs PIVOT only

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources