Update specific columns in SQL Server table and ignoring Null values

Question

I have a database table with many columns. Is there sql that will update records in that table where all or only specific columns are handled in such a way that if NULL is passed for any column value that the existing value not be changed?

Currently I can use solutions like these

UPDATE table
    SET column1 = COALESCE(@param1, column1),
        column2 = COALESCE(@param2, column2),
        ...
    WHERE id = @id

or

UPDATE table
set   column1 = isnull(@param1,column1),
      column2 = isnull(@param2,column2)

They both works well, though sometimes I want to explicitly save null in any column and I can't do it with the above solutions. How?

ΩmegaMan · Accepted Answer · 2022-03-29 18:32:28Z

3

One approach is to declare two parameters for each column, the first contains the value, the second is a bit instructs the query to insert null explicitly.

Example

create table example (column1 nvarchar(255), column2 nvarchar(255))

create procedure pUpdate(
    @column1 nvarchar(255)  = null,
    @nullColumn1 tinyint    = 0,
    @column2 nvarchar(255)  = null,
    @nullColumn2 tinyint    = 0
    ) as
    BEGIN
    
    update example 
        set column1 = Case When @nullcolumn1 = 1 
                           Then NULL ELSE IsNull(@column1, column1) End
        set column2 = Case When @nullcolumn2 = 1 
                           Then NULL ELSE IsNull(@column2, column2) End

    END

Then when calling from code, you only have to pass the parameters that you know need updating, or explicitely set the @nullcolumn to force a null.

edited Mar 29, 2022 at 18:32

ΩmegaMan

32.2k13 gold badges110 silver badges138 bronze badges

answered May 13, 2012 at 13:32

James

973 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Waqas Anwar Over a year ago

James thanks for this solution, it looks very easy solution to implement the only issue is that for large tables we need to create so many extra parameters in update Stored Procedures but so far this is the best solution I have until unless someone else came with another solution. Thanks once again.

Collectives™ on Stack Overflow

Update specific columns in SQL Server table and ignoring Null values

1 Answer 1

Example

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Example

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related