How do I update a column only if a different column's value is false, null or doesn't matc-CodePudding

In my table I have two columns

ClosedDate DateTime null
IsClosed bit null

If the incoming value of @IsClosed is true and the current value of IsClosed either null or false then I would like to update the value of ClosedDate to GetDate() like this

ClosedDate = GETDATE()

Here is what I have tried but it is not working.

ALTER PROCEDURE[dbo].someProcedure
@IsClosed   bit,
Update dbo.Foo
SET
//Other fields to be set 
ClosedDate = CASE WHEN IsClosed = 0 OR IsClosed = NULL AND IsClosed <> @IsClosed THEN GETDATE() END,
    IsClosed = @IsClosed
WHERE myId = @myId

Where am I going wrong?

CodePudding user response：

Set CloseDate to itself if it must not change, otherwise it will be set to NULL.

UPDATE dbo.Foo
SET
    ClosedDate = CASE WHEN ISNULL(IsClosed, 0) = 0 AND IsClosed <> @IsClosed
                 THEN GETDATE()
                 ELSE ClosedDate
                 END,
    IsClosed = @IsClosed
WHERE myId = @myId

Note, if you were setting only one column, or if both columns must be either updated or not updated together, you would simply add all the conditions to the WHERE-clause.

UPDATE dbo.Foo
SET
    ClosedDate = GETDATE()
    IsClosed = @IsClosed
WHERE
    myId = @myId AND
    ISNULL(IsClosed, 0) = 0 AND
    IsClosed <> @IsClosed

Also, do not test IsClosed = 0 OR IsClosed = NULL. This will not work as expected, because of the NULL propagation. Use ISNULL(IsClosed, 0) = 0 instead or COALESCE(IsClosed, 0) = 0 (I am not sure if SQL-Server 2008 already knows ISNULL).

CodePudding user response：

One catch here is the case where @IsClosed (the input) is 0 and IsClosed (the column) is NULL. The existing @IsClosed <> IsClosed can have unexpected results.

This should do what you need:

ALTER PROCEDURE[dbo].someProcedure
@IsClosed   bit, @MyId int
AS
Update dbo.Foo
SET
    ClosedDate = current_timestamp
    IsClosed = @IsClosed
WHERE myId = @myId AND COALESCE(IsCosed,0) = 0 and @IsClosed = 1