ALTER TABLE my_table ADD @column INT

sql-server alter-table
Andreas picture Andreas · Apr 10, 2010 · Viewed 42.1k times · Source

If i want to use a variable as name of the new column, is this posible in MS SQL?

Example that dont work:

ALTER TABLE my_table ADD @column INT

This worked great for me:

EXEC ('ALTER TABLE my_table ADD ' + @column + ' INT')

Answer

Oded picture Oded · Apr 10, 2010

This is possible using dynamic sql to build your DDL and using the EXEC command to execute the string.

Declare @SQL VarChar(1000)

SELECT @SQL = 'ALTER TABLE my_table ADD ' + @column + ' INT'

Exec (@SQL)

See this article.

I will also add that the moment you venture to the land of dynamic sql, you need to take care to not expose yourself to SQL Injection attacks. Always clean up the parameters coming in.

As Philip mentions - think long and hard before doing this. The fact that it is possible does not make it a good thing...

Erland Sommarskog wrote an extensive article about using dynamic sql - The curse and blessings of dynamic SQL which I recommend reading fully.

Related questions

Check if a temporary table exists and delete if it exists before creating a temporary table

I am using the following code to check if the temporary table exists and drop the table if it exists before creating again. It works fine as long as I don't change the columns. If I add a column later, …

Read More
Altering a column: null to not null

I have a table that has several nullable integer columns. This is undesirable for several reasons, so I am looking to update all nulls to 0 and then set these columns to NOT NULL. Aside from changing nulls to 0, data must …

Read More
Rename column SQL Server 2008

I am using SQL Server 2008 and Navicat. I need to rename a column in a table using SQL. ALTER TABLE table_name RENAME COLUMN old_name to new_name; This statement doesn't work.

Read More