T-SQL 条件更新 (v2)

Question

我有一个表：

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

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

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

现在我很困惑如何构建条件 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

...但这似乎不是正确的语法，因为 CASE 必须位于分配的右侧。

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

Answer 1

创建语句所需的语法是：

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

如果在所有建议之后您仍然想坚持它。

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

Answer 2

到目前为止，最好的选择是使用显式 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

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

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

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

Answer 3

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 @SubjectChanged = 1 
    update Message set Subject = @Subject where MessageID = @MessageID
if @BodyChanged = 1 
    update Message set Body = @Body where MessageID = @MessageID

Answer 4

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

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

Answer 5

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

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

然后，您可以通过以下方式构建更新：

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

Answer 6

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

Answer 7

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

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

Answer 8

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

更好的方法是简单地对每一项更新使用单独的存储过程。