T-SQL 条件更新 (v2)

T-SQL conditional UPDATE (v2)

提问by Andrey

I have a table:

我有一张桌子：

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

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:

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

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

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

现在我很困惑如何构建条件UPDATE语句。我的第一个想法是使用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

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

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

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

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

回答by Ralph Lavelle

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.

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

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.

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

回答by Remus Rusanu

Your best bet, by far, is to use explicit IF statements:

到目前为止，您最好的选择是使用显式 IF 语句：

IF @subjectHasChanged = 1 and @bodyHasChanged = 1
 UPDATE Messages SET Subject = @subject, Body = @body 
   WHERE MessageId = @MessageId
ELSE IF @subjectHasChanged = 1
 UPDATE Messages SET Subject = @subject WHERE MessageId = @MessageId
ELSE IF @bodyHasChanged
 UPDATE Messages SET Body = @body WHERE MessageId = @MessageId

From a performance point of view, nothing beats this. Because SQL can see during query compilation that you only update Body, or Subject, or both, it can generate the appropriate plan, for instance not even bothering to open (for update) the non-clustered index you have on Subject (if you have one, of course) when you only update Body.

从性能的角度来看，没有什么比这更好的了。因为 SQL 可以在查询编译期间看到您只更新 Body 或 Subject 或两者，所以它可以生成适当的计划，例如甚至不必费心打开（更新）您在 Subject 上的非聚集索引（如果您有一个，当然）当你只更新 Body.

From a code code quality point of view, this is disaster, a nightmare to maintain. But acknowledging the problem is 80% solving the problem :) . You can use code generation techniques for instance to maintain such problem procedures.

从代码代码质量的角度来看，这是灾难，是维护的噩梦。但承认问题是解决问题的 80% :) 。例如，您可以使用代码生成技术来维护此类问题过程。

Another viable approach is actually to use dynamic SQL, construct the UPDATE in the procedure and use sp_executesql. It has its own set of problems, as all dynamic SQL has. There are resources about dynamic SQL problems, and there are workarounds and solutions, see The Curse and Blessings of Dynamic SQL.

另一种可行的方法实际上是使用动态SQL，在过程中构造UPDATE 并使用sp_executesql。它有自己的一系列问题，就像所有动态 SQL 一样。有关于动态 SQL 问题的资源，也有变通方法和解决方案，请参阅动态 SQL 的诅咒和祝福。

回答by Adam Robinson

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

That should really be all you need. However, if you truly can't update the field if it hasn't changed, then you'll have to do it in separate statements.

这真的应该是你所需要的。但是，如果您确实无法在未更改的情况下更新该字段，则您必须在单独的语句中进行更新。

if @SubjectChanged = 1 
    update Message set Subject = @Subject where MessageID = @MessageID
if @BodyChanged = 1 
    update Message set Body = @Body where MessageID = @MessageID

回答by Ron Savage

Seems to me like you are wasting a lot of effort. If you retrieve the six values, display them to the user (in some user interface) and they can change some variable number of them and hit a "save" button - then just update all 6 fields every time, getting the new values from the user input fields.

在我看来，你正在浪费很多精力。如果您检索六个值，将它们显示给用户（在某些用户界面中），他们可以更改其中的一些可变数量并点击“保存”按钮 - 然后每次都更新所有 6 个字段，从用户输入字段。

Some may not have changed, but so what. Much simpler code that way.

有些可能没有改变，但那又怎样。这样的代码要简单得多。

回答by Rodrigo

Use DEFAULT values for the stored procedure parameters.

对存储过程参数使用 DEFAULT 值。

create proc UpdateMessage(
  @MessageID int,  -- mandatory
  @Subject nvarchar(100) = NULL, 
  @Body nvarchar(max) = NULL)

Then, you can structure your update in this way:

然后，您可以以这种方式构建更新：

Update [Message] 
SET 
[Subject] = ISNULL(@Subject, [Subject]),
Body = ISNULL(@Body, Body)
WHERE MessageID = @MessageID

回答by dreadwail

CREATE PROCEDURE UpdateMessage
  @MessageID int, 
  @Subject nvarchar(100), 
  @Body nvarchar(max),
AS
BEGIN
    if(@Subject is null or @Subject='')
        SELECT @Subject=Subject FROM Message WHERE MessageID=@MessageID
    if(@Body is null or @Body='')
        SELECT @Body=Body FROM Message WHERE MessageID=@MessageID
    UPDATE Message SET Subject=@Subject, Body=@Body WHERE MessageID=@MessageID
END
GO

回答by Waleed Al-Balooshi

I am not sure if this is the best way to do it, but maybe you can try

我不确定这是否是最好的方法，但也许你可以试试

IF @SubjectChanged = 1 THEN
   BEGIN
      UPDATE [Message]
      SET [Subject] = @Subject
      WHERE MessageID = @MessageID     
   END
END

IF @BodyChanged = 1 THEN
   BEGIN
      UPDATE [Message]
      SET Body = @Body
      WHERE MessageID = @MessageID
   END
END

回答by Joe Phillips

I would highly recommend using Adam Robinson's method if you require this to be in a single stored procedure.

如果您需要将它放在单个存储过程中，我强烈建议使用 Adam Robinson 的方法。

Even better would be to simply use separate stored procedures for each one of these updates.

更好的做法是简单地为这些更新中的每一个使用单独的存储过程。