SQL 存储过程：如果变量不为空，则更新语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7111907/
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 Stored Procedure: If variable is not null, update statement
提问by Jake
I have an update statement in a stored procedure that looks generally like this:
我在存储过程中有一个更新语句，它通常如下所示：
Update [TABLE_NAME]
Set XYZ=@ABC
Is there a good way to only trigger the update statement if the variable is not null or the value -1?
如果变量不为空或值为-1，是否有一种好方法只触发更新语句？
Similar to an IF NOT EXISTS...INSERTquestion.
类似于一个IF NOT EXISTS...INSERT问题。
Thank you so much.
非常感谢。
回答by James Hill
回答by Adam Caviness
Another approach when you have many updates would be to use COALESCE:
当您有很多更新时，另一种方法是使用 COALESCE：
UPDATE [DATABASE].[dbo].[TABLE_NAME]
SET
[ABC] = COALESCE(@ABC, [ABC]),
[ABCD] = COALESCE(@ABCD, [ABCD])
回答by Thomas Hansen
Yet another approach is ISNULL().
另一种方法是 ISNULL()。
UPDATE [DATABASE].[dbo].[TABLE_NAME]
SET
[ABC] = ISNULL(@ABC, [ABC]),
[ABCD] = ISNULL(@ABCD, [ABCD])
The difference between ISNULL and COALESCE is the return type. COALESCE can also take more than 2 arguments, and use the first that is not null. I.e.
ISNULL 和 COALESCE 之间的区别在于返回类型。COALESCE 也可以采用 2 个以上的参数，并使用第一个不为空的参数。IE
select COALESCE(null, null, 1, 'two') --returns 1
select COALESCE(null, null, null, 'two') --returns 'two'
