Case When NULL - John Cantley

How do I do something like this?

Case Field1
When NULL then 'AddressUpdate'
Else Field1
End As MessageType

When i do the preceeding I get Null instead of 'AddressUpdate'

Thanks
JC

Case When NULL - Gert-Jan Strik

That is because the expression Field1 = NULL evaluates to UNKNOWN and
therefore, the CASE WHEN will fall through to the next WHEN (or in this
case ELSE).

What you need is Field1 IS NULL, as in

CASE WHEN Field1 IS NULL THEN 'AddressUpdate' ELSE Field1 END AS
MessageType

Or, more compact

COALESCE(Field1, 'AddressUpdate') AS MessageType


--
Gert-Jan
SQL Server MVP

Case When NULL - Aaron Bertrand [SQL Server MVP]

This is like saying WHERE column1 = NULL.  You can't make direct comparisons
to NULL, since NULL is unknown, equality can't be proven (or disproven).
The result is still unknown, so CASE in this case (no pun intended) will not
yield true or false.  You need to say:

MessageType = CASE
WHEN Field1 IS NULL THEN 'AddressUpdate'
ELSE Field1
END

Like Gert-Jan says, this can be written more concisely, as in:

MessageType = COALESCE(Field1, 'AddressUpdate')