I have a table like this

I want to store column name in varibale @myColumn and then use Update command. How can this be done ? Is it possible ?

    DECLARE @myColumn varchar(20)
    SET @myColumn = 'State'
    UPDATE Country SET @myColumn = 'Florida' WHERE Id = 1

CodePudding user response：

Yes, it's possible with some dynamic SQL. If you have complete control in the process, you can try this solution:

Let's create a temp table to show:

create table #temp ([id] int, [state] varchar(10));

Insert into #temp
SELECT 1 as ID, null as [state]

select * from #temp

Now that's created, let's try

DECLARE @myColumn varchar(20), @sql varchar(400)
SET @myColumn = '[state]'

set @sql = CONCAT('Update #temp SET ',@myColumn ,'= ''FLORIDA'' where ID = 1')

exec(@sql)

Check the results

select * from #temp

If you don't have complete control over the process, you need to save your code from SQL Injection.

CodePudding user response：

To dynamically use the name of a column you'll need Dynamic SQL.

Here's an example:

DECLARE @DynSql nvarchar(max)
      , @DynParams nvarchar(max)
      , @UpdateSql nvarchar(max)
      , @UpdateParams nvarchar(max);
DECLARE @myColumn varchar(20)
      , @myColumnValue varchar(20)
      , @myId INT;

SET @UpdateSql = 'UPDATE Country ' CHAR(10)
         'SET [COLUMN] = @Value'   CHAR(10) 
         'WHERE Id = @Id';
SET @UpdateParams = N'@Value varchar(20), @Id int';

SET @myColumn = 'State';
SET @myColumnValue = 'Florida';
SET @myId = 1;
SET @DynSql = REPLACE(@UpdateSql, '[COLUMN]', QUOTENAME(@myColumn));
SET @DynParams = @UpdateParams;

-- select @DynSql As DynSql;
EXECUTE sp_executesql @DynSql, @DynParams
      , @Value = @myColumnValue
      , @Id = @myId;

1 rows affected

SELECT * FROM Country WHERE ID = 1;

Demo on db<>fiddle here