Alter Table question

• Adrianne Osborne

  Valued Member

  Points: 58

  More actions

  July 22, 2004 at 9:18 am

  #108887

  Is there any way to execute an alter table statement to add a column where the column name is the value of a variable?

  I've got a table with industry_code, region, year, qtr, firm_count.  And I have to output industry_code, [year+qtr], [year+qtr], [year+qtr] where the total firm_count for a selected region is in it's matching [year+qtr] field.  I have a third table that contains TimeID, year, qtr.

  Any help would be great! -Adrianne

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737751

  More actions

  July 22, 2004 at 9:53 am

  #515718

  Not directly. You can use dynamic sql,

   

  select @cmd = 'alter table mytable add ' + (year + 1) + ' int' ...

  exec ( @cmd)

   

• Adrianne Osborne

  Valued Member

  Points: 58

  More actions

  July 22, 2004 at 10:22 am

  #515723

  Thanks for the info.  I tried what you said, using the code below, but got an error message: Could not find stored procedure 'alter table test add 1990q4 int'.

  Here's the code I used:

  declare @year char(4)

  declare @qtr char(1)

  declare @col varchar(50)

  select @year = '1990', @qtr='4'

  select @col = 'alter table test add ' + @year + 'q' + @qtr + ' int'

  exec @col

  Any ideas? -Adrianne

• s-158333

  SSC Enthusiast

  Points: 104

  More actions

  July 22, 2004 at 10:40 am

  #515728

  use parentheses around @col as in exec(@col) - they're needed in this case

• Adrianne Osborne

  Valued Member

  Points: 58

  More actions

  July 22, 2004 at 10:50 am

  #515730

  Thanks!  That did the trick.

  -Adrianne