CODEWITHSUNDEEP
sql-server
Kiel
Kiel

Reputation: 203

SQL Server : VAR Value to Column Name

I am moving over old system into a new one and there is some really stupid stuff going on but anyway I need to do this;

UPDATE tblSomething 
SET Column3 = 'Something' 
WHERE ID = 58

By using something like this;

DECLARE @No INT = 3

UPDATE tblSomething 
SET Column+@No = 'Something 
WHERE ID = 58

So I am trying to use a value as a column name. Please help...

Upvotes: 0

Views: 38

Answers (1)

EzLo
EzLo

Reputation: 14209

You can't use variables as object names directly, you will have to use Dynamic SQL for this.

DECLARE @No INT = 3
DECLARE @DynamicSQL VARCHAR(MAX) = '
    UPDATE 
        tblSomething 
    SET 
        Column' + + CONVERT(VARCHAR(10), @No) + ' = ''Something'' WHERE ID = 58'


PRINT (@DynamicSQL)

EXEC (@DynamicSQL)

Beware of inserting unsafe values on your dynamic sql (SQL Injection). It's not much problem with integers, but for databases, tables or columns you should use QUOTENAME() and limit the VARCHAR length to a decent value.

Upvotes: 2

Related Questions