How to use variable in an update statement in sql

Question

i have a big problem. I'm coding a simply update statement but using variable for the set. Here is the code:

DECLARE @VAR1 nvarchar(255) = 'PRIMARYNAME'
DECLARE @VAR2 nvarchar(255) = 'ANDREW'
DECLARE @counter INT = 5


    WHILE @counter <> 0
    BEGIN   
        PRINT 'PRE VAR1: '+@VAR1
        PRINT 'PRE VAR2: '+@VAR2    
        UPDATE [TABTARGET]
        SET @VAR1 = @VAR2   
        PRINT 'POST VAR1: '+@VAR1
        PRINT 'POST VAR2: '+@VAR2 
        SET @counter = @counter -1
    END

And here is the output:

PRE VAR1: PRIMARYNAME
PRE VAR2: ANDREW

(5 rows affected)
POST VAR1: ANDREW
POST VAR2: ANDREW
PRE VAR1: ANDREW
PRE VAR2: ANDREW

As you can see the set instruction instead of taking the value of the variable, make an assignment... How to avoid these problem? I need to use the variable, because the update is build by a dynamic sql declaration.

Thank you all

What RDBMS are you using (Postgres, MySQL)? Can you post the entire DDL of your function? — ravioli
– ravioli, Commented Sep 20, 2019 at 8:08

Ed Bangga · Accepted Answer · 2019-09-20 08:24:16Z

There's no way you can do this unless you use string sql.

DECLARE @VAR1 nvarchar(255) = 'PRIMARYNAME'
DECLARE @VAR2 nvarchar(255) = 'ANDREW'
DECLARE @strSQL nvarchar(255) 
DECLARE @counter INT = 5


    WHILE @counter <> 0
    BEGIN   
        PRINT 'PRE VAR1: '+@VAR1
        PRINT 'PRE VAR2: '+@VAR2    

        SET @strSQL = N'UPDATE test
        SET name = @VAR1'   
        PRINT @strSQL
        exec sp_executesql @strSQL , N'@VAR1 nvarchar(255)', @VAR1
        PRINT 'POST VAR1: '+@VAR1
        PRINT 'POST VAR2: '+@VAR2 
        SET @counter = @counter -1
    END

If you @VAR1 is a dynamic column, use this instead.

DECLARE @VAR1 nvarchar(255) = 'PRIMARYNAME'
DECLARE @VAR2 nvarchar(255) = 'ANDREW'
DECLARE @strSQL nvarchar(255) 
DECLARE @counter INT = 5


    WHILE @counter <> 0
    BEGIN   
        PRINT 'PRE VAR1: '+@VAR1
        PRINT 'PRE VAR2: '+@VAR2    

        SET @strSQL = N'UPDATE test
        SET  '+ QUOTENAME(@VAR1) + ' = @VAR2'   
        PRINT @strSQL
        exec sp_executesql @strSQL , N'@VAR1 nvarchar(255), @VAR2 nvarchar(255)', @VAR1, @VAR2
        PRINT 'POST VAR1: '+@VAR1
        PRINT 'POST VAR2: '+@VAR2 
        SET @counter = @counter -1
    END

Collectives™ on Stack Overflow

How to use variable in an update statement in sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest