SQL Server – Updating a Field with a Partial Pass Parameter

sql servert-sql

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?

Best Answer

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
Related Question