Conditionally set values in UPDATE statement.

Question

I would like to have a stored procedure that will update values in a table row depending on whether or not the parameters are provided. For example, I have a situation where I want to update all the values, but also a situation where I'm only required to update two values. I was hoping to be able to do this with only one procedure, rather than writing two, which doesn't particularly appeal to me. The best I have managed to come up with myself is something like the following:

CREATE PROCEDURE dbo.UpdatePerson
@PersonId INT,
@Firstname VARCHAR(50) = NULL,
@Lastname VARCHAR(50) = NULL,
@Email VARCHAR(50) = NULL
AS
BEGIN
    SET NOCOUNT ON

UPDATE Person 
Set 
    Firstname = COALESCE(@Firstname, Firstname),
    Lastname = COALESCE(@LastName, Lastname),
    Email = COALESCE(@Email, Email)
    WHERE PersonId = @PersonId

END

I realize that the values will be updated each time anyway, which isn't ideal. Is this an effective way of achieving this, or could it be done a better way?

Answer 1

I think your code is fine. The only thing I would add is a check for the case when all three params are NULL, in which case no update should be done.

Answer 2

SQL Server does actually have some logic to deal with non updating updates.

More details than you probably wanted to know!