Menu
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?
482
To do a conditional update depending on whether the current value of a column matches the condition, you can add a WHERE clause which specifies this. The database will first find rows which match the WHERE clause and then only perform updates on those rows.
The SQL UPDATE Query is used to modify the existing records in a table. You can use the WHERE clause with the UPDATE query to update the selected rows, otherwise all the rows would be affected.
UPDATE operations with subqueries that reference the same table object are supported only if all of the following conditions are true: The subquery either returns a single row, or else has no correlated column references. The subquery is in the UPDATE statement WHERE clause, using Condition with Subquery syntax.
Conditional update applies to scenarios where high-concurrency applications are updated. In these scenarios, old_value may be updated by other clients. If you use conditional update, the current value is updated to new_value only when the current value is equal to old_value.
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.
126
SQL Server does actually have some logic to deal with non updating updates.
More details than you probably wanted to know!
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
