CODEWITHSUNDEEP
sqlsql-servert-sql
Alex
Alex

Reputation: 553

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.

Upvotes: 55

Views: 123546

Answers (2)

oshaath
oshaath

Reputation: 1

Update [Users]
set    FirstName = iif(ISNULL(ltrim(rtrim(@FirstName)), '')='', FirstName, @FirstName),
       ....
Where  ...

Upvotes: -1

Cheruvian
Cheruvian

Reputation: 5867

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.

Upvotes: 126

Related Questions