Select CASE returning NULL value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sbowman@comcast.net - 26 Apr 2008 16:34 GMT

I have the following CASE statement in a view:

(SELECT CASE
WHEN ISNULL([dbo].[tblContact].[JobTitleID], NULL) = NULL
THEN [dbo].[f_ContactNameFL]([dbo].[tblContact].[ContactID])
ELSE [dbo].[f_ContactNameFL]([dbo].[tblContact].[ContactID]) + ', ' +
[dbo].[tlkpJobTitle].[Title]
END) AS ContactNameAndTitle

It works perfectly when dbo.tblContact.JobTitleID is NOT null. When it
is null it should return a contact name but it is returning null. The
problem is not f_ContactNameFL. There is another column in the same
view that uses that function and it works perfectly. What am I doing
wrong?

Thanks,
Shelley

Reply to this Message

Plamen Ratchev - 26 Apr 2008 18:44 GMT

You cannot check NULL for equality (it is unknown, so it doesn't equal
another unknown). The correct way to check if expression is NULL is using IS
NULL:

CASE WHEN [dbo].[tblContact].[JobTitleID] IS NULL THEN ...

The code that you posted can be simplified using the COALESCE function:

[dbo].[f_ContactNameFL]([dbo].[tblContact].[ContactID]) +
COALESCE(', ' + [dbo].[tlkpJobTitle].[Title], '') AS ContactNameAndTitle

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message