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

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

Oh gosh... Fail. I should have read more closely. w3schools.com/sql/sql_alter.asp
@Chris well you shouldn't have started at w3schools, anyway. :-)

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

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

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

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Related