ALTER TABLE my_table ADD @column INT

问题:

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

回答1:

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.

回答2:

Have a look at (EXECUTE (Transact-SQL))

CREATE TABLE MyTable(
        ID INT
)
GO
SELECT * FROM MyTable
GO
DECLARE @column VARCHAR(100)
SET @column = 'MyNewCol'
EXEC('ALTER TABLE MyTable ADD ' + @column + ' INT')
GO
SELECT * FROM MyTable
GO
DROP TABLE MyTable

回答3:

alter procedure sp_check_table_column
(
    @field_name varchar(max),
    @data_type varchar(max),
    @mandatory varchar(max)
)
as
if not exists (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '<table_name>' and COLUMN_NAME = @field_name)
begin
 declare @sql varchar(max)
 set @sql = ('ALTER TABLE <table_name> ADD ' + @field_name + ' ' + @data_type + ' ' + @mandatory)
 exec (@sql) 
end