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.
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.
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 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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With