CODEWITHSUNDEEP
sqlsql-servert-sql
Kerri Brown
Kerri Brown

Reputation: 1187

Conditionally set values in UPDATE statement

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?

Upvotes: 5

Views: 739

Answers (2)

Martin Smith
Martin Smith

Reputation: 453278

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

More details than you probably wanted to know!

Upvotes: 4

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171411

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.

Upvotes: 4

Related Questions