用于在 SQL Server 2005 中更新存储过程中的表的 if-else 条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1672069/
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
if-else condition for update a table in a stored procedure in SQL Server 2005
提问by Minati Behera
I want to update some data in a specified case, else these columns are not to be updated.
我想在指定的情况下更新一些数据，否则这些列不会被更新。
What can I write code in a stored procedure for this?
我可以在存储过程中为此编写什么代码？
回答by Guffa
You can use a caseto control whether you assign a new value or keep the old value.
您可以使用 acase来控制是分配新值还是保留旧值。
update <sometable>
set field = case when <condition> then <newvalue> else field end
where <condition>
Example:
例子：
update questions
set reply = case when @input is not null then @input else reply end
where answer = 42
回答by priyanka.sarkar
Use Case statement in Update clause
Update 子句中的 Use Case 语句
like
喜欢
SQL Statement #6
SQL 语句 #6
UPDATE titles
SET price =
CASE
WHEN (price < 5.0 AND ytd_sales > 999.99)
THEN price * 1.25
WHEN (price < 5.0 AND ytd_sales < 1000.00)
THEN price * 1.15
WHEN (price > 4.99 AND ytd_sales > 999.99)
THEN price * 1.2
ELSE price
END
Taken from SQL SERVER UPDATE
Also you can go with if..else statement
你也可以使用 if..else 语句
If you would have been in SQL SERVER 2008, you could have avail the flavor of MERGE statement
如果您使用过 SQL SERVER 2008，则可以利用 MERGE 语句的风格
回答by j.a.estevan
May be you can build the condition in the update command and easily run more than one update with the diferent conditions. It may not be the most elegant way but it is prety eficient. It depends of your needs.
也许您可以在更新命令中构建条件，并轻松地使用不同的条件运行多个更新。这可能不是最优雅的方式，但它非常有效。这取决于您的需求。
UPDATE table SET field=value WHERE <<condition>>
UPDATE table SET field=value2 WHERE <<condition2>>
回答by Faruz
Just an example:
只是一个例子：
IF @a <= 0
BEGIN
UPDATE table SET counter = @a, name = 'Minati'
END
ELSE
BEGIN
UPDATE table SET name = 'Minati'
END
