Implementing IF Condition Within a T-SQL UPDATE Statement

Question

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?

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

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