no update for database row if parameter is null

Sometimes you want write a stored procedure to update a database entry, but you want be able to leave some columns unchanged. If you don’t need to set them back to null you can use the following code:

UPDATE TABLE
  SET Column1 = ISNULL(@parameter1, Column1), Column2 = ISNULL(@parameter2, Column2)
  WHERE PrimaryKey = @KEY

The command ISNULL uses the first parameter, if it is not null, or the second parameter if the first is null. When you pass a value, this value is used, but when you pass null the current value of this column is used, so it overwrites it with the same value.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.