Post reply

Alter Table question

  • 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

  • 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)

     

  • 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

  • July 22, 2004 at 10:40 am

    #515728

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

  • July 22, 2004 at 10:50 am

    #515730

    Thanks!  That did the trick.

    -Adrianne

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply