如果参数不为 null 或为空，则 SQL 更新 [英] SQL Update if parameter is not null or empty

问题描述

我搜索了一些方法来检查 SQL Server 参数是否不为 null 或为空，但我不确定在更新多列时使用它的最佳方法是什么:

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

然后我在更新之前添加了一个 IF 子句，它是这样工作的，但我不确定这是否是最好的方法，如果我必须更新它会很长几列.

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
   ...
   ...

如果值为 Null 或 Empty 我不需要更新，只需保留数据库中的原始值即可.

If the value is Null or Empty I don't need to update, just keep the original value in the database.

推荐答案

如果它是空白的，我不确定你要实现什么，但我会尝试使用 IsNull() 我不认为有一个IsBlank()，但是自己写应该不会太难

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

仅使用 IsNull 您的查询将类似于...

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.

这篇关于如果参数不为 null 或为空，则 SQL 更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！