47

I searched some ways to check if a SQL Server parameter is not null or empty but I'm not sure what's the best way to use this when updating several columns:

I had this code at first that was updating without checking for empty or Null values:

UPDATE [Users] 
SET FirstName = @firstname, City = @city, Address = @address, ....
WHERE ID = @iduser

Then I added an IF clause before updating, it is working this way but I'm not sure if that's the best way to do it, it is going to be long if I have to update several columns.

--Check if parameter is not null or empty before updating the column
IF (@firstname IS NOT NULL AND @firstname != '')
   UPDATE [Users] 
   SET FirstName = @firstname 
   WHERE ID = @iduser

IF (@city IS NOT NULL AND @city != '')
   UPDATE [Users] 
   SET City = @city 
   WHERE ID = @iduser
   ...
   ...

If the value is Null or Empty I don't need to update, just keep the original value in the database.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Alex
  • 473
  • 1
  • 4
  • 6

2 Answers2

115

not sure what you are trying to achieve if it is blank, but I would try using IsNull() I don't think there is an IsBlank(), but it shouldn't be too hard to write yourself

Using just IsNull your query would look something like...

Update [Users]
set    FirstName = IsNull(@FirstName, FirstName),
       City = IsNull(@City, City)
       ....
Where  ...

this will Update the row with the param value if they are NOT null, otherwise update it to itself aka change nothing.

Cheruvian
  • 5,628
  • 1
  • 24
  • 34
  • 5
    Thank you for your fast response, you guide me to the correct answer. I ended up using: ISNULL(NULLIF(@City, ''), City): http://stackoverflow.com/questions/334108/how-do-i-check-if-a-sql-server-string-is-null-or-empty I'm not sure if I should close this – Alex Aug 15 '14 at 00:07
  • @Alex `NULLIF` using for ***VARCHAR columns***, not for another _datatypes_ – Kiquenet Feb 18 '16 at 13:31
  • Can we alse use Coalesce() for this? – Master Yoda Feb 28 '18 at 11:38
  • 6
    I am using `COALESCE` with postgresql and it works! Simply do this: `UPDATE table SET column =COALESCE(newValue, column) WHERE condition = true` – addicted Apr 12 '20 at 13:41
  • 1
    How is this correct? COALESCE and ISNULL do _not_ handle empty strings like nulls: https://i.imgur.com/6nNMy7G.png – Adam Spriggs Aug 04 '20 at 23:09
  • for googlers: If you are using PL SQL, could use `City = NVL(:City, City)` instead. See: [NVL](https://docs.oracle.com/en/database/oracle/oracle-database/20/sqlrf/NVL.html) – Rohim Chou Oct 27 '20 at 02:03
-1
Update [Users]
set    FirstName = iif(ISNULL(ltrim(rtrim(@FirstName)), '')='', FirstName, @FirstName),
       ....
Where  ...
Jeff Schaller
  • 2,352
  • 5
  • 23
  • 38
oshaath
  • 1