What should a SQL Parameter be in a update statement be when it's not required

Question

As the the question says, what should I set the value of a parameter to if I don't want it to update the DB?

e.g.

UPDATE table1
SET col1 = @someval ,
    col2 = @someotherval
WHERE col3 = 1;

If in my c# logic I decide that col2 does not need to be updated because the value being passed in is null, what should I set the parameters value so that it does not update that column?

I have to use parameterised queries and I don't want to edit the original update statement.

Answer 1

Set the parameter for col2 to be what is presently. To have made the decision within C# you must know what it is I'd think or then you'd have to get it first.

This is one of the main attractions of using an ORM like NHibernate which deals with these issues for you.

Answer 2

If you don't want a value to be updated, the only way is to entirely remove it from the SQL statement. There is no value that does what you need, you have to have different statements for that.

The only alternative to that is querying the current value from the DB and setting this value once again, which is at least ugly...

Answer 3

If col2 cannot ever be updated to NULL, try this

update table1 
set col1 = @someval , col2 = ISNULL(@someotherval, col2)
where col3 = 1;

Answer 4

You could use coalesce() which is ANSI standard (ISNULL isn't), but would change your update statement slightly, e.g.:

update table1 
   set col1 = coalesce(@someval, col1),
       col2 = @someotherval
 where col3 = 1;

This would update it to what it is presently.

Answer 5

You can try this...

UPDATE  table1 
SET     col1 = @someval, 
        col2 = ISNULL(@someotherval, col2)
WHERE   col3 = 1;

This will not update the column if you pass null, it does however prevent you from setting the column to null which may or may not be ok.

Answer 6

If you do not want to update the value of the column, you should either somehow remove it from your update query, using a StringBuilder to filter out conditions.

Or you could try looking at:

public SqlParameter Add(
 string parameterName,
 SqlDbType sqlDbType,
 int size,
 string sourceColumn
);

Which I think, might actually pull the value from the source. Thus setting the value to the value it was before. You might have to change the direction of the parameter to be both Input and Output.