DDL statements with variables for table and column names

Question

In my stored procedure, I make a temp_tbl and want to add several columns in a cursor or while loop. All works fine the cursor (the creation of a temp_bl but I can´t add the column when the column string is in a varchar variable.

WHILE @@FETCH_STATUS = 0
BEGIN       
SET @webadressenrow = 'Webadresse_'+CAST(@counter as nchar(10))

    ALTER TABLE IVS.tmpBus
        ADD @webadressenrow varchar(500) Null

    fetch next from cur_web into @webadressen
    SET @counter = @counter + 1
END

The code above results in a syntax error, while this code works:

WHILE @@FETCH_STATUS = 0
BEGIN       
SET @webadressenrow = 'Webadresse_'+CAST(@counter as nchar(10))

    ALTER TABLE IVS.tmpBus
     ADD SOMECOLUMNAME varchar(500) Null

    fetch next from cur_web into @webadressen
    SET @counter = @counter + 1
END

Can anybody give me a syntax hint to this small problem?

Answer 1

You won't be able to parameterise the ALTER TABLE statement but you could build up the SQL and execute it something like this:

declare @sql nvarchar(max)
set @sql = 'create table IVS.tmpBus ( '

select
    @sql = @sql + 'Webadresse_' +
        row_number() over ( order by col ) +
        ' varchar(500) null, '
from sourceData

set @sql = substring(@sql, 1, len(@sql) - 2) + ' )'
exec @sql

Be careful about security/SQL-Injection attacks though.