christopher clark christopher clark - 1 month ago 17
SQL Question

CASE When a different value is null

I've looked at multiple examples, but I can't see why this SQL statement says "Incorrect syntax near IS and THEN"

I am trying to set the status_date column based on if the page has been changed.
So, if

@page_has_not_changed
is null then
status_date
should equal `status_date.

When
@page_has_not_changed
is
1
then
status_date
should equal
status_date
again.

When
@page_has_not_changed
is
0
then
status_date
should equal
GETDATE()


Here is my query.

SET status_ID = @status_id,
page_has_not_changed = @page_has_not_changed,
status_date = CASE @page_has_not_changed
--is and null below has syntax issue
WHEN IS NULL Then status_date
WHEN 1 Then status_date
ELSE GETDATE() END,

Answer

You need to use the alternate case syntax:

SET     status_ID = @status_id,
        page_has_not_changed = @page_has_not_changed,
        status_date = CASE
                  --is and null below has syntax issue
                    WHEN @page_has_not_changed IS NULL Then status_date
                    WHEN @page_has_not_changed = 1 Then status_date
                    ELSE GETDATE() END

According to the documentation, the Simple CASE expression (the one you were using), "Allows only an equality check". IS NULL is not an equality check.