how to catch NULL values using case statement
I'd use the ISNULL function - it will return the value given if the field is NULL:
select contactid,Title,FirstName,MiddleName,case ISNULL(MiddleName, 'NULLVALUE')when 'R.' then 'Robert'when 'B.' then 'Bids'when 'J.' then 'John'when 'NULLVALUE' then 'New Name'else 'No Name'end, LastName from Person.Contact
Sorry to post 7 years later, but I've been trying to find a solution for Interbase / Firebird and this post kept popping up. None of the solutions here work because there is no ISNULL, so I figured I'd help anyone else who might come here looking for that:
select contactid,Title,FirstName,MiddleName,case COALESCE(MiddleName, 'NULLVALUE')when 'R.' then 'Robert'when 'B.' then 'Bids'when 'J.' then 'John'when 'NULLVALUE' then 'New Name'else 'No Name'end, LastName from Person.Contact