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