updating a field with a partial pass parameter

Question

I am working on a table that has 24 columns named pp1, pp2... pp24.

I would like to pass in a parameter that would be used to help identify the column to be updated.

• one parameter is the value (8)
• one parameter is the column (5)
• one parameter is the rowID (256)

short stored proc...

update 'pp'+ [column_parameter] 
set value to [value_parameter] 
where rowID = [rowID_parameter]

Can the concatenation of the partial column name be combined with a parameter?

Answer 1

That's really a bad idea, I don't understand why you need to do so, and it can't be done the way you are looking for, but if you going to do this, I suggest to declare 24 variable + 1 for the RowID.

For example:

CREATE PROCEDURE MyProc
  @RowID INT,
  @Col1 VARCHAR(45),
  @Col2 INT
  -- In your case you will need 24 variable, each one represent a value for a column
AS
BEGIN
  --Optional: Check if @RowID IS NULL and RAISERROR first
  DECLARE @SQL NVARCHAR(MAX) = N'UPDATE [T]
                                 SET [Col1] = ISNULL(@Col1, Col1),
                                     [Col2] = ISNULL(@Col2, Col2)
                                 WHERE [RowID] = @RowID';
  EXECUTE sp_executesql @SQL,
                        N'@Col1 VARCHAR(45), @Col2 INT, @RowID INT',
                        @Col1,
                        @Col2,
                        @RowID;
END

Then call your procedure, and pass NULLs for columns you don't want to be updated. That's the only way I can think of it.

Sample Demo

---

UPDATE:

You don't even need for DynamicSQL, so your SP may looks like

CREATE PROCEDURE MyProc
  @RowID INT,
  @Col1 VARCHAR(45),
  @Col2 INT
  -- In your case you will need 24 variable, each one represent a value for a column
AS
BEGIN
  --Optional: Check if @RowID IS NULL and RAISERROR ROLLBACK TRANSACTION
  UPDATE T
  SET Col1 = ISNULL(@Col1, Col1),
      Col2 = ISNULL(@Col2, Col2)
  WHERE RowID = @RowID;
END

Answer 2

It can be done by performing a "no-op" update on the other columns.

declare @t table
(
    pp1 int,
    pp2 int,
    pp3 int
);

insert @t values (5, 6, 7);

select * from @t;


declare @column int = 2;
declare @value  int = 9;


update @t
set
    pp1 = case when @column = 1 then @value else pp1 end,
    pp2 = case when @column = 2 then @value else pp2 end,
    pp3 = case when @column = 3 then @value else pp3 end
;

select * from @t;

Before update:

        pp1         pp2         pp3
----------- ----------- -----------
          5           6           7

After update:

        pp1         pp2         pp3
----------- ----------- -----------
          5           9           7

What I've shown will work just as well with normal tables and a stored procedure. If the columns are of different data types you could declare @value as sql_variant.

Setting the columns to their existing values should not end up with additional writes. See here for an analysis.

While this may well work the whole thing has code smells all through it. Having numbered columns suggests the tables are not normalized. Columns should have one, fixed purpose so code knows exactly which one to reference in every circumstance. I'm guessing the application allows for run-time specified fields and this is the implementation?

Answer 3

Since I did not do the table structure (legacy code) I needed a quick and dirty solution - so I went with a dynamic SQL.

Thank you all for the answers and comments.