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

发布于 2024-09-10 03:35:20 字数 459 浏览 2 评论 0原文

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

这是我想要完成的一个简单的伪 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

通过阅读 IF 条件在 T-SQL 中的工作原理，我可以看出，为了实现与上述伪代码相同的结果，我必须创建每个 IF 条件的 UPDATE 语句 - 这是我试图避免必须做的事情。

是否可以仅使用一个 UPDATE 语句根据条件动态设置字段/列？ - 如果是这样，怎么办？

原文

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

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

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

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

余厌 2024-09-17 03:35:20

CASE 表达式

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

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

回复收藏 0 原文

冷夜 2024-09-17 03:35:20

我认为这会很有用：

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

你喜欢它吗？
享受。

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.

回复收藏 0 原文

~没有更多了~