Error using ALTER TABLE ... ADD COLUMN

Question

I get our developers to easily update our database. The way we do this is to create dynamic queries in which they define variables at the top, and the query uses variables for everything else. I have used many recommendations outside of Stackoverflow, but I cannot 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 an error message:

Invalid syntax next to the keyword "COLUMN".

Answer 1

As an error message indicates that this is incorrect syntax. The somewhat funny keyword COLUMN not valid when adding a column.

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

More on this in Curse and blessings of dynamic SQL: working with dynamic table and column names

Answer 2

I highly recommend not to do this due to the impact of SQL injection. However, if necessary, 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