1

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?

  • This can be done with dynamic SQL, but why would you want to? It gets nasty – scsimon Jan 11 at 19:51
  • 2
    As @scsimon mentioned, you need to do this with dynamic T-SQL. Read this first: sommarskog.se/dynamic_sql.html – Max Vernon Jan 11 at 21:01
  • @scsimon How that can be done even with Dynamic SQL? Would you check also for the datatype of the columns? – Sami Jan 11 at 21:09
  • Are all 24 columns the same datatype? – RDFozz Jan 11 at 21:19
  • Nope, it'd error out if they submitted the wrong datatype for the column, just as it would if it wasn't dynamic @Sami. – scsimon Jan 11 at 21:26
3

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
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?

0

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.