在UPDATE语句中有条件地设置值

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?

我意识到每次都会更新这些值,这并不理想。这是实现这一目标的有效方法,还是可以采用更好的方式?

2 个解决方案

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

我认为你的代码很好。我要添加的唯一内容是检查所有三个参数都为NULL时的情况,在这种情况下不应该进行更新。

#2

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

SQL Server确实有一些逻辑来处理非更新更新。

More details than you probably wanted to know!

比你想知道的更多细节!

#1