Hi,
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)
Thanks! Andrey