adding a column with variable name

Question

I am trying to dynamically add som columns to 2 tables if they dont already exist. My problem is, that the name of the column depends on another column's value.

But apparently the following is not allowed, why?

declare @inputs int; 
set @inputs = (select inputs from campaigns where id = 102) + 1; 
update campaigns set inputs = @inputs where id = 102; 
if col_length('campaigns', 'input' + @inputs) is null alter table campaigns add input' + @inputs + ' ntext null; 
if col_length('campaigns', 'input' + @inputs + 'text') is null alter table campaigns add input' + @inputs + 'ivocall ntext null; 
if col_length('rapports', 'input' + @inputs) is null alter table rapports add input' + @inputs + ' ntext null; 
if col_length('rapports', 'input' + @inputs + 'values') is null alter table rapports add input' + @inputs + 'values ntext null; 
update campaigns set input' + @inputs + ' = '1||test||||0||0||0||0||0||2||0' where id = 102

I get the following errors

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ' + @inputs + '.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ' + @inputs + '.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ' + @inputs + '.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ' + @inputs + '.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ' + @inputs + '.

having column names depend on table values is almost certainly bad normalization at best — Ghost
– Ghost, Commented Jul 2, 2012 at 17:49
True.. And that will be corrected later on. But right now I need this to work somehow — PonyTricks
– PonyTricks, Commented Jul 2, 2012 at 17:50
@inputs its a int variable, and you're using like a literal string. Try to Cast it before use it. +CAST(@inputs as varchar(50))+ — Gonzalo.-
– Gonzalo.-, Commented Jul 2, 2012 at 17:51

Sean · Accepted Answer · 2012-07-02 17:54:23Z

1

Look at this:

if col_length('campaigns', 'input' + @inputs) is null 
    alter table campaigns 
    add input' + @inputs + ' ntext null;

The third line is just not right, at best it should be:

if col_length('campaigns', 'input' + @inputs) is null 
    alter table campaigns 
    add 'input' + @inputs ntext null;

However, even that is not going to work. You're probably better off creating the whole DDL statement as a string, and then executing that. Something like:

set @sql = 'alter table campaigns add column input' + @inputs + ' ntext null'
exec (@sql)

answered Jul 2, 2012 at 17:54

Sean

15.2k17 gold badges82 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

adding a column with variable name

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related