Select CASE returning NULL value

[sbowman@comcast.net]

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 ContactNameAndT itle

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

[Plamen Ratchev]

Re: Select CASE returning NULL value

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 ContactNameAndT itle

HTH,

Plamen Ratchev

http://www.SQLStudio.com