How to check for NULL in a CASE statement with a Scalar Function?

SELECT CASE 
         WHEN dbo.fnCarerResponse('') IS NULL 
         THEN 'Pass'
         ELSE 'Fail'
       END

You are using the wrong style of CASE - you need to use CASE WHEN <expression> THEN not CASE <expression> WHEN <expression> then:

SELECT CASE 
 WHEN dbo.fnCarerResponse('') IS NULL
 THEN 'Pass'
 ELSE 'Fail'
END

You can use this way too:

SELECT   CASE ISNULL(dbo.fnCarerResponse(''),'NULLVALUE')
           WHEN 'NULLVALUE' THEN 'Pass'
           ELSE 'Fail'
         END

SELECT  CASE 
          WHEN dbo.fnCarerResponse('') is NULL THEN 'Pass'
          ELSE 'Fail'
        END

Related