如果参数不为 null 或为空，则 SQL 更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25319248/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Update if parameter is not null or empty
提问by Alex
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:
我搜索了一些方法来检查 SQL Server 参数是否不为 null 或为空，但我不确定在更新几列时使用它的最佳方法是什么：
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 IFclause 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.
然后我IF在更新之前添加了一个子句，它是这样工作的，但我不确定这是否是最好的方法，如果我必须更新几列，时间会很长。
--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.
如果值为 Null 或 Empty 我不需要更新，只需将原始值保留在数据库中即可。
回答by Cheruvian
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()我认为没有IsBlank()，但是自己编写应该不会太难
Using just IsNullyour query would look something like...
仅使用IsNull您的查询看起来像......
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.
如果它们不为空，这将使用参数值更新行，否则将其更新为自身，也就是什么都不改变。
回答by MISSIRIA
Try this code SQLnative it's work for me very well :
试试这个代码SQLnative 它对我来说很好用：
UPDATE gp_customer
SET ville = 'NO'
WHERE ville
IS NULL
OR ville = ''
Update just NULL value or empty.
仅更新 NULL 值或空值。
