Alex Alex - 3 months ago 10
SQL Question

SQL Update if parameter is not null or empty

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.

Answer

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.