T-SQL 条件更新 (v2) [英] T-SQL conditional UPDATE (v2)

问题描述

我有一张桌子:

Message (MessageID int, Subject nvarchar(100), Body nvarchar(max))

在 UI 上更新消息后，我调用存储过程来更新该表.在某些情况下，用户可能只更新主题，在其他情况下只更新正文.我希望这个存储过程只更新已更改的内容，因此我还传递了显示主题或正文是否已更新的标志:

After a message is being updated on UI, I call a stored proc to update that table. In some cases user might update just subject, in other cases just body. I want this stored proc to only update what has changed, so I'm also passing flags showing whether subject or body has been updated:

create proc UpdateMessage(
  @MessageID int, 
  @Subject nvarchar(100), 
  @Body nvarchar(max),
  @SubjectChanged bit,
  @BodyChanged bit)

现在我很困惑如何构建条件 UPDATE 语句.我的第一个想法是使用 CASE:

And now i'm confused how to build the conditional UPDATE statement. My first thought was to use CASE:

Update [Message] 
SET 
CASE WHEN @SubjectChanged = 1 THEN [Subject] = @Subject ELSE 1=1 END,
CASE WHEN @BodyChanged = 1 THEN Body = @Body ELSE 1=1 END,
WHERE MessageID = @MessageID

... 但这似乎不是正确的语法，因为 CASE 必须是赋值的右侧.

... but that doesn't seem to be a correct syntax as CASE has to be the right side of an assigment.

任何想法我怎么能做到这一点?(请记住，实际上有 6 个参数可以更新，而不是两个)

Any ideas how I could do that? (And keep in mind that in reality there are 6 parameters that can be updated, not two)

推荐答案

创建语句所需的语法是:

The syntax required to create your statement is:

Update [Message] 
SET    [Subject] = CASE WHEN @SubjectChanged = 1 THEN @Subject ELSE [Subject] END,
       Body = CASE WHEN @BodyChanged = 1 THEN @Body ELSE Body END
WHERE  MessageID = @MessageID

如果您在所有建议之后仍想坚持下去.

if you still want to stick to it after all the suggestions.

注意如果您省略 CASE 语句的 ELSE [Subject] 部分，而不是忽略 UPDATE，它会将字段设置为 NULL.

N.b. if you leave out the ELSE [Subject] part of the CASE statements, instead of ignoring the UPDATE it sets the field to NULL.

这篇关于T-SQL 条件更新 (v2)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！