T-SQL conditional UPDATE (v2)

Question

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:

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 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

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

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

Answer 1

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.

Answer 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

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