Help with dynamic column names 1

[scuttleButt]

I am using sql server 2000. I am creating a table in a dts with one column for each day.

I have declared my variables and found the last day of the month. I am trying to set up the columns in a "while" loop:

Declare @idx int, @eom int
Set @idx = 1
set @eom = (select day(dateadd(dd,-1,cast(convert(char(7),dateadd(mm,1,getdate()),120)+'-01' as datetime))))

CREATE TABLE dbo.eodReport (
clinics varchar (15) NULL,
fcc char (5) NULL
) ON [PRIMARY]

While @idx <= @eom+1
BEGIN
ALTER TABLE eodReport ADD @idx int
END

I get an error: &quot;Incorrect syntax near '@idx'&quot;.

It doesn't seem to like the &quot;@&quot;. What am I doing wrong or am I not able to do what I want?

Thanks!

 

[sunila7]

Hi,

Try this..........


Declare @idx int, @eom int
Declare @SQL varchar(1000)
Set @idx = 1
set @eom = (select day(dateadd(dd,-1,cast(convert(char(7),dateadd(mm,1,getdate()),120)+'-01' as datetime))))

CREATE TABLE dbo.eodReport (
clinics varchar (15) NULL,
fcc char (5) NULL
) ON [PRIMARY]

While @idx <= @eom+1
BEGIN
SET @SQL= 'ALTER TABLE eodReport ADD ' + convert(varchar,@idx) + ' int'
Print @SQL
EXEC(@SQL)
END


Sunil

 

[misgirlny]

Try this:

Execute ('ALTER TABLE eodReport ADD' + @idx + ' int')

 

[scuttleButt]

Thank you Sunil, you were very helpful. It worked with the addition of brackets, it didn't like having just a number for a column name.

Declare @idx int, @eom int
Declare @SQL varchar(1000)
Set @idx = 1
set @eom = (select day(dateadd(dd,-1,cast(convert(char(7),dateadd(mm,1,getdate()),120)+'-01' as datetime))))

CREATE TABLE dbo.eodReport (
clinics varchar (15) NULL,
fcc char (5) NULL
) ON [PRIMARY]

While @idx <= @eom+1
BEGIN
SET @SQL= 'ALTER TABLE eodReport ADD [' + convert(varchar,@idx) + '] int'
Print @SQL
EXEC(@SQL)
Set @idx = @idx+1
END

select * from eodReport

 

[scuttleButt]

p.s. and with the addition of incrementing @idx -- donnamaxey [santa2]