CODEWITHSUNDEEP
sqlsql-servervariables
Robertuzzo
Robertuzzo

Reputation: 169

How to use variable in an update statement in sql

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

Upvotes: 0

Views: 415

Answers (1)

Ed Bangga
Ed Bangga

Reputation: 13006

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

Upvotes: 2

Related Questions