在Dynamic SQL中使用Variable作为列名的一部分

I am trying to use a variable as part of a column name in a dynamic SQL statement.

我试图在动态SQL语句中使用变量作为列名的一部分。

I have 4 columns that are called Player1Action, Player2Action, Player3Action and Player4Action.

我有4列叫做Player1Action，Player2Action，Player3Action和Player4Action。

Depending on the varaible @CurrentPlayerId, I want to update the relevant player column with some data.

根据可变的@CurrentPlayerId，我想用一些数据更新相关的播放器列。

So for example, if CurrentPlayerId contains 1, then update column Player1Action.

因此，例如，如果CurrentPlayerId包含1，则更新列Player1Action。

I realise I could achieve this with a series of IF statements, but I would like to do it in a cleaner manner.

我意识到我可以通过一系列IF语句实现这一点，但我想以更清洁的方式实现。

I have the following code, but I think my escaping is causing issues. I have re-written this a view times but I don't seem to be able to get it to work.

我有以下代码，但我认为我的转义会导致问题。我已经重新编写了一个查看时间，但我似乎无法让它工作。

DECLARE
@CurrentPlayerId INT,
@CurrentPlayerBetAmount nvarchar(MAX),
@stmt nvarchar(MAX);

SET @CurrentPlayerId = 1
SET @CurrentPlayerBetAmount = 100

SET @stmt = N'UPDATE HandCurrent SET ''Player'' ''+'' @CurrentPlayerId ''+'' ''Action'' = 1 '','' ''Player'' ''+'' @CurrentPlayerId ''+'' ''ActionSize'' = @CurrentPlayerBetAmount'

EXECUTE sp_executesql @stmt

If I run this as a select I get the following returned.

如果我将其作为选择运行，则返回以下内容。

UPDATE HandCurrent SET 'Player' '+' @CurrentPlayerId '+' 'Action' = 1 ',' 'Player' '+' @CurrentPlayerId '+' 'ActionSize' = @CurrentPlayerBetAmount

3 个解决方案

#1

DECLARE
@CurrentPlayerId INT,
@CurrentPlayerBetAmount INT,
@stmt nvarchar(MAX);

SET @CurrentPlayerId = 1
SET @CurrentPlayerBetAmount = 100

SET @stmt = N'UPDATE HandCurrent SET 
            Player' + CAST(@CurrentPlayerId AS NVARCHAR(10)) + 'Action = 1, 
            Player' + CAST(@CurrentPlayerId AS NVARCHAR(10)) + 'ActionSize = ' 
           + CAST(@CurrentPlayerBetAmount AS NVARCHAR(10))

EXECUTE sp_executesql @stmt

Juan Carlos had it right only issue is datatypes of variables @CurrentPlayerId is an INT so you would need to cast it to NVARCHAR() https://www.mssqltips.com/sqlservertip/3014/concatenation-of-different-sql-server-data-types/.

胡安卡洛斯说得对，唯一的问题是变量的数据类型@CurrentPlayerId是一个INT所以你需要将它转换为NVARCHAR（）https://www.mssqltips.com/sqlservertip/3014/concatenation-of-different-sql-server -data-类型/。

To be consistent you should have declared @CurrentPlayerBetAmount as INT as well. If you do then you need to cast it too.

为了保持一致，你应该将@CurrentPlayerBetAmount声明为INT。如果你这样做，你也需要施放它。

However, you could also simply DECLARE both as NVARCAHR(MAX) and then single quote the value when setting it such as:

但是，您也可以简单地将两者都声明为NVARCAHR（MAX），然后在设置时单引号，例如：

DECLARE
@CurrentPlayerId NVARCHAR(MAX),
@CurrentPlayerBetAmount NVARCHAR(MAX),
@stmt nvarchar(MAX);

SET @CurrentPlayerId = '1'
SET @CurrentPlayerBetAmount = '100'

SET @stmt = N'UPDATE HandCurrent SET 
            Player' + @CurrentPlayerId + 'Action = 1, 
            Player' + @CurrentPlayerId + 'ActionSize = ' + @CurrentPlayerBetAmount

EXECUTE sp_executesql @stmt

Or My preference if you are going to change a record anyway would be just to do the update statement if you have 4 columns. You could also think of nth Normalizing and making the columns Rows....

或者，如果您要更改记录，我的偏好只是在您有4列时执行更新语句。您还可以考虑nth Normalizing并使列Rows ....

DECLARE @CurrentPlayerId INT = 1
DECLARE @CurrentPlayerBetAmount INT = 100

UPDATE HandCurrent
    Player1Action = CASE WHEN @CurrentPlayerId = 1 THEN 1 ELSE PlayerAction1 END
    ,Player1ActionSize = CASE WHEN @CurrentPlayerId = 1 THEN @CurrentPlayerBetAmount ELSE Player1ActionSize END
    ,Player2Action = CASE WHEN @CurrentPlayerId = 2 THEN 1 ELSE PlayerAction2 END
    ,Player2ActionSize = CASE WHEN @CurrentPlayerId = 2 THEN @CurrentPlayerBetAmount ELSE Player2ActionSize END
    ,Player3Action = CASE WHEN @CurrentPlayerId = 3 THEN 1 ELSE PlayerAction3 END
    ,Player3ActionSize = CASE WHEN @CurrentPlayerId = 3 THEN @CurrentPlayerBetAmount ELSE Player3ActionSize END
    ,Player4Action = CASE WHEN @CurrentPlayerId = 4 THEN 1 ELSE PlayerAction4 END
    ,Player4ActionSize = CASE WHEN @CurrentPlayerId = 4 THEN @CurrentPlayerBetAmount ELSE Player4ActionSize END
WHERE
    ????

Note you are actually updating the entire table is that what you desire?

注意你实际更新整个表是你想要的吗？

#2

dont quote the variables.

不要引用变量。

SET @stmt = N'UPDATE HandCurrent SET 
            Player' + @CurrentPlayerId + 'Action = 1, 
            Player' + @CurrentPlayerId + 'ActionSize = ' + @CurrentPlayerBetAmount

#3

Speaking of cleanest way to do this, IMHO the best approach is to use sp_executesql with parameters, implying the @CurrentPlayerId and @CurrentPlayerBetAmount are input parameters of the given code:

说到最干净的方法，恕我直言，最好的方法是使用带参数的sp_executesql，暗示@CurrentPlayerId和@CurrentPlayerBetAmount是给定代码的输入参数：

Declare @sql nvarchar(256)
    , @sql1 nvarchar(256) = N'update HandCurrent
    Set Player1Action =@value,
    Player1ActionSize =@size'
    , @sql2 nvarchar(256) = N'update HandCurrent
    Set Player2Action =@value,
    Player2ActionSize =@size'
    , @params nvarchar (128)
    , @CurrentPlayerId int
    ;

Select @sql = case @CurrentPlayerId when 1 then @sql1 when 2 then @sql2 else '' end;
If @sql <> '' begin
  set @params = N'@value int, @size int';
  execute sp_executesql @sql, @params,
        @value =1,
        @size = @CurrentPlayerBetAmount
        ;
End

#1

DECLARE
@CurrentPlayerId INT,
@CurrentPlayerBetAmount INT,
@stmt nvarchar(MAX);

SET @CurrentPlayerId = 1
SET @CurrentPlayerBetAmount = 100

SET @stmt = N'UPDATE HandCurrent SET 
            Player' + CAST(@CurrentPlayerId AS NVARCHAR(10)) + 'Action = 1, 
            Player' + CAST(@CurrentPlayerId AS NVARCHAR(10)) + 'ActionSize = ' 
           + CAST(@CurrentPlayerBetAmount AS NVARCHAR(10))

EXECUTE sp_executesql @stmt