在T-SQL UPDATE语句中实现IF条件。

Using T-SQL, I would like to execute an UPDATE statement that will SET columns only if the corresponding variables are defined.

使用T-SQL，我想执行一个UPDATE语句，它只在定义相应的变量时才设置列。

Here's a simple pseudo-tsql example of what I'm trying to accomplish:

下面是一个简单的伪tsql示例，说明我要实现的目标:

--Declaring vars
@ID int,
@Name nvarchar(20),
@Password nvarchar(10)

--Run the update
UPDATE User
SET
IF LEN(@NAME) > 0
  Name = @Name,
IF LEN(@Password) > 0
  Password = @Password

From what I can tell by reading how IF conditions work in T-SQL, in order to accomplish the same result as the above pseudo code, I would have to create an UPDATE statement for every IF condition - which is what I'm trying to avoid having to do.

通过阅读T-SQL中的IF条件如何工作，我可以知道，为了实现与上面的伪代码相同的结果，我必须为每个IF条件创建一个更新语句——这正是我要避免的。

Is it possible to dynamically SET fields/columns based on a condition using only one UPDATE statement? - If so, how?

是否可以根据一个条件使用一个UPDATE语句动态地设置字段/列?——如果是这样,如何?

2 个解决方案

#1

The CASE expression

这样表达

UPDATE User
SET
    Name = CASE WHEN LEN(@NAME) > 0 THEN @Name ELSE Name END,
    Password = CASE WHEN LEN(@Password) > 0 THEN @Password ELSE Password END
WHERE
   ...

#2

I think this will be useful:

我认为这将是有用的:

Create PROCEDURE [dbo].[CodeUpdate] 
(
    @Id int, 
    @Name nVarChar(150)=null, 
    @IsActive bit =null, 
    @IsSystem bit=null
) AS 
BEGIN 
    UPDATE [dbo].[Code] SET 
        [Name] = CASE WHEN @Name is null THEN [Name] ELSE @Name END,
        [IsActive] = CASE WHEN @IsActive is null THEN [IsActive] ELSE @IsActive END,
        [IsSystem] = CASE WHEN @IsSystem is null THEN [IsSystem] ELSE @IsSystem END
    WHERE  ID = @Id
End

Do you like it? Enjoy.

你喜欢它吗?享受。

#1