Using Variable as part of column name in Dynamic SQL

Question

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

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

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

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

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

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

@JuanCarlosOropeza I have tried doing that, but the result doesn't really explain to me which its going wrong. I can post the output of the select if that helps? — AntonZ
– AntonZ, Commented Dec 2, 2016 at 15:33

Matt · Accepted Answer · 2016-12-02 18:36:12Z

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/.

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

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

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....

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?

Juan Carlos Oropeza · Accepted Answer · 2016-12-02 15:54:13Z

2

dont quote the variables.

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

edited Dec 2, 2016 at 15:54

answered Dec 2, 2016 at 15:37

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

5 Comments

SS_DBA Over a year ago

Don't quote the operators either. OP had them double single quotes.

AntonZ Over a year ago

Thanks for the suggestion, this doesnt work for me, as it seems to show a qoute is missing.

AntonZ Over a year ago

@JuanCarlosOropeza now its complaining about a conversion error. "Msg 245, Level 16, State 1, Line 9 Conversion failed when converting the nvarchar value 'UPDATE HandCurrent SET Player' to data type int."

Juan Carlos Oropeza Over a year ago

Player1ActionSize is string?

Matt Over a year ago

@CurrentPlayerId is an INT and would need to be cast to NVARCHAR()...mssqltips.com/sqlservertip/3014/… o change variable to nvarchar instead of INT. Which is how CurrentPlayerBetAmount is

Yuriy Tsarkov · Accepted Answer · 2016-12-02 18:04:50Z

0

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:

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

answered Dec 2, 2016 at 18:04

Yuriy Tsarkov

2,5882 gold badges17 silver badges29 bronze badges

2 Comments

Matt Over a year ago

While I agree with the paraemterizing dynamic sql when you can the issue is that the parameter is being used to dynamically identify the column names to be updated. So that wouldn't work for this case, but it would work for the BetAmount as that could be parameterized

Yuriy Tsarkov Over a year ago

in this particular case yes, but if the @CurrentPlayerId is an input parameter, then there are can be much more logic in a UDF that depends on it. In this case we can assign value to @sql and do another actions

Collectives™ on Stack Overflow

Using Variable as part of column name in Dynamic SQL

3 Answers 3

1 Comment

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related