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.

回答1:

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.

回答2:

Try this code SQL native it's work for me very well :

UPDATE gp_customer 
SET ville = 'NO'
WHERE ville 
IS NULL
OR ville = ''

Update just NULL value or empty.