将具有派生列名称的列添加到SQL Server 2014中的现有表

DECLARE
    @countFlag INT,
    @maxNum INT = 5,
    @addingName NVARCHAR(6)

SET @countFlag = 1

WHILE (@countFlag <= @maxNum)
    BEGIN
        SET @addingName = 'name' + CAST(@countFlag AS NVARCHAR(2))

        ALTER TABLE TableName
        ADD
        @addingName NVARCHAR(30)

        SET @countFlag = @countFlag + 1
    END
========================================================

This is called at the beginning of a set of procedures. @maxNum is actually passed in based on a question to the operator and changes the 'shape' of an existing db to include more columns. I would like to have the resulting column names be something like "name1" "name2" etc. but I am getting an "Incorrect syntax near '@addingName'" after the ADD statement when I execute it. What am I doing wrong here?

这在一组程序的开头被调用。 @maxNum实际上是根据问题传递给操作员并更改现有数据库的“形状”以包含更多列。我希望得到的列名称类似于“name1”“name2”等。但是当我执行它时,我在ADD语句之后得到“@addingName'附近的”语法不正确“。我在这做错了什么?

1 个解决方案

#1

You cannot do it in that way, you should compose the query dynamically and execute it with Exec:

您无法以这种方式执行此操作,您应该动态编写查询并使用Exec执行它:

DECLARE @sqlCommand varchar(200)

SET @sqlCommand = 'ALTER TABLE TableName ADD ' + @addingName + ' NVARCHAR(30) '

EXEC (@sqlCommand)