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'.

Answer 1

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

Answer 2

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