T-SQL условное ОБНОВЛЕНИЕ (v2)

У меня есть таблица:

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

После того, как сообщение обновляется в пользовательском интерфейсе, я вызываю сохраненный proc для обновления этой таблицы. В некоторых случаях пользователь может обновлять только тему, в других случаях - только тело. Я хочу, чтобы этот сохраненный proc обновил только то, что изменилось, поэтому я также передаю флажки, показывающие, обновлен ли объект или тело:

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 параметров, которые можно обновить, а не два)

Ответ 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

если вы все еще хотите придерживаться его после всех предложений.

N.b. если вы не укажете часть ELSE [Subject] в операторах CASE, вместо игнорирования UPDATE оно устанавливает значение NULL.

Ответ 2

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

Ответ 3

Лучше всего на самом деле использовать явные инструкции 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, или Subject, или и то, и другое, он может генерировать соответствующий план, например, даже не пытаясь открыть (для обновления) некластеризованный индекс, который у вас есть на Subject (если у вас есть один, конечно), когда вы только обновляете Body.

С точки зрения качества кода кода это катастрофа, кошмар для поддержания. Но признать проблему на 80% решить проблему:). Вы можете использовать методы генерации кода, например, для поддержания таких проблемных процедур.

Другим жизнеспособным подходом является использование динамического SQL, создание UPDATE в процедуре и использование sp_executesql. Он имеет свой собственный набор проблем, как и все динамические SQL. Есть ресурсы о динамических проблемах SQL, и есть обходные пути и решения, см. Проклятие и благословения динамического SQL.

Ответ 4

Мне кажется, что вы тратите много сил. Если вы извлекаете шесть значений, отобразите их пользователю (в каком-то пользовательском интерфейсе), и они могут изменить некоторое количество переменных из них и нажмите кнопку "Сохранить" - тогда просто обновляйте все 6 полей каждый раз, получая новые значения из полей ввода пользователя.

Некоторые, возможно, не изменились, но что именно. Это гораздо проще.

Ответ 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

Ответ 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 [email protected]
    if(@Body is null or @Body='')
        SELECT @Body=Body FROM Message WHERE [email protected]
    UPDATE Message SET [email protected], [email protected] WHERE [email protected]
END
GO

Ответ 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

Ответ 8

Я бы очень рекомендовал использовать метод Адама Робинсона, если вы хотите, чтобы это было в одной хранимой процедуре.

Еще лучше было бы просто использовать отдельные хранимые процедуры для каждого из этих обновлений.