Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 533
Alex Avatar asked Aug 14 '14 23:08

Alex


People also ask

Is not null and empty in SQL?

The IS NOT NULL condition is used in SQL to test for a non-NULL value. It returns TRUE if a non-NULL value is found, otherwise it returns FALSE. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Is NULL or empty in SQL?

NULL is used in SQL to indicate that a value doesn't exist in the database. It's not to be confused with an empty string or a zero value. While NULL indicates the absence of a value, the empty string and zero both represent actual values.

Is is not NULL and != The same in SQL?

<> is Standard SQL-92; != is its equivalent. Both evaluate for values, which NULL is not -- NULL is a placeholder to say there is the absence of a value.


1 Answers

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.

like image 150
Cheruvian Avatar answered Oct 04 '22 10:10

Cheruvian