Error using ALTER TABLE ... ADD COLUMN syntax

Question

I'm making a way for our developers to easily update our database. The way we're doing this is by creating dynamic queries where they define the variables at the top and the query uses the variables for everything else. I've used many recommendations off Stackoverflow, but can't get this to work.

USE MyDatabase
DECLARE @TABLE VARCHAR(200) = 'MyTable'
DECLARE @COLUMN VARCHAR(200) = 'MyColumn'
DECLARE @DATATYPE VARCHAR(200) = 'VARCHAR(200)'

IF COL_LENGTH(@TABLE, @COLUMN) IS NULL
BEGIN
    DECLARE @SQL as NVARCHAR(MAX) = 'ALTER TABLE ' + @TABLE + ' ADD COLUMN ' 
      + @COLUMN +' '+ @DATATYPE
    EXEC SP_EXECUTESQL @SQL
END

I get the error:

Incorrect syntax near the keyword 'COLUMN'.

Looking at the ALTER TABLE documentation, you can't specify the COLUMN keyword there. You have to specify it when dropping a column, but it is not legal syntax when adding. It's one of those SQL Server inconsistencies that nobody can really justify but is also unlikely to ever get fixed. — Aaron Bertrand
– Aaron Bertrand, Commented Jun 19, 2013 at 21:04

Martin Smith · Accepted Answer · 2013-06-19 21:09:20Z

7

As the error message indicates that is the wrong syntax. Somewhat confusingly the COLUMN keyword is not permitted when adding a column.

Also VARCHAR(200) should really be SYSNAME to cope with all possible valid names (currently equivalent to nvarchar(128)) and use QUOTENAME to correctly escape any object names containing ]

More about this is in The Curse and Blessings of Dynamic SQL: Dealing with Dynamic Table and Column Names

edited Jun 19, 2013 at 21:09

answered Jun 19, 2013 at 21:03

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

2 Comments

Chris Over a year ago

Oh gosh... Fail. I should have read more closely. w3schools.com/sql/sql_alter.asp

Aaron Bertrand Over a year ago

@Chris well you shouldn't have started at w3schools, anyway. :-)

SeanPrice · Accepted Answer · 2013-06-19 21:08:47Z

3

I highly suggest against doing this due to exposure to SQL injection. However, if you must, remove the word COLUMN from your script and it should work.

USE MyDatabase
DECLARE @TABLE VARCHAR(200) = 'MyTable'
DECLARE @COLUMN VARCHAR(200) = 'MyColumn'
DECLARE @DATATYPE VARCHAR(200) = 'VARCHAR(200)'

IF COL_LENGTH(@TABLE, @COLUMN) IS NULL
BEGIN
    DECLARE @SQL as NVARCHAR(MAX) = 'ALTER TABLE ' + @TABLE + ' ADD ' + @COLUMN     +' '+ @DATATYPE
    EXEC SP_EXECUTESQL @SQL
END

answered Jun 19, 2013 at 21:08

SeanPrice

4622 silver badges8 bronze badges

1 Comment

Aaron Bertrand Over a year ago

Note that the code is for internal use by developers - they're not taking random form input from strangers.

Collectives™ on Stack Overflow

Error using ALTER TABLE ... ADD COLUMN syntax

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related