T-Sql Stored Procedure to Dynamically Add a Column

问题:

Possible Duplicate:
ALTER TABLE my_table ADD @column INT

Can anyone tell me if it is possible to have a stored procedure that is effectively this:

USE [dbname]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [Schema].[CreateColumn]
    @Table varchar(255),
    @ColumnName varchar(255)
AS
BEGIN
    BEGIN TRANSACTION
        SET QUOTED_IDENTIFIER ON
        SET ARITHABORT ON
        SET NUMERIC_ROUNDABORT OFF
        SET CONCAT_NULL_YIELDS_NULL ON
        SET ANSI_NULLS ON
        SET ANSI_PADDING ON
        SET ANSI_WARNINGS ON
    COMMIT

    BEGIN TRANSACTION
        ALTER TABLE [Schema].@Table ADD @ColumnName varchar(255) NULL
        ALTER TABLE [Schema].@Table SET (LOCK_ESCALATION = TABLE)
    COMMIT
END
GO

Basically, I could pass in a table name and column name and it would create it on that table with proper transactions.

回答1:

I'm sorry, it's not possible. See this website.

I'm sorry, but that is not true.

It is, however, clumsy. You are almost as well off simply executing in-line SQL (if form client code) or simply composing an SQL statement each time, as you need to define the Table name, the Column name, The Column DataType, and the default value each time you execute (unless you hard-code some of those parameters in i.e. the column data type will ALWAYS be VarChar(255).

Play with variations on this, and see if you find what you are looking for. However, there are probably better ways to acheive what you are trying to do:

DECLARE @sql NChar(4000);

SET @Sql = N'ALTER TABLE [Schema].' 
    + @TableName + ' ADD ' 
    + @ColumnName + ' ' 
    + @ColumnType + ' ' 
    + @InitialValue

EXECUTE sp_executesql @sql
GO

回答2:

I'm sorry, it's not possible. See this website.