-4

I have a WHERE clause that I want to use a CASE expression in. However, my CASE expression needs to check if a field IS NULL.

If the @UserRole variable value = 'Analyst', then the SupervisorApprovedBy column value must be NULL. Otherwise, I am saying return all data i.e. SupervisorApprovedBy = SupervisorApprovedBy.

What do I need to change to the below?

WHERE SupervisorApprovedBy = 
CASE 
    WHEN @UserRole = 'Analyst' THEN NULL
    ELSE SupervisorApprovedBy
END

I cannot use = for NULL. Tested and does not work.

I would want all rows returned including those where SupervisorApprovedBy IS NOT NULL.

closed as off-topic by McNets, Tom V - Team Monica, Erik Darling, dezso, SqlWorldWide Aug 1 '17 at 12:37

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Tom V - Team Monica, Erik Darling, dezso
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    Please, could you add some sample data and the desired result? – McNets Aug 1 '17 at 9:58
  • Could someone please explain why this is on hold and why so many down votes? It's really a simple question as to how can I use CASE in a WHERE clause to check if a field IS NULL. Thanks – K09 Aug 1 '17 at 19:58
6

I think you are making it harder than it should be.

If @UserRole is 'Analyst' then also SupervisorApprovedBy should be null? Else return everything?

WHERE (@UserRole = 'Analyst' AND SupervisorApprovedBy IS NULL ) 
OR (ISNULL(@UserRole, '') <> 'Analyst')
12

The problem is likely the comparison to NULL, as explained in David Spillett's answer above. When @UserRole = 'Analyst', the comparison SupervisorApprovedBy = NULL will give UNKNOWN (and the row won't pass the WHERE test).

You can rewrite with nested CASE expressions:

WHERE 1 =
  CASE 
    WHEN @UserRole = 'Analyst' THEN 
        CASE WHEN SupervisorApprovedBy IS NULL THEN 1 END
    WHEN SupervisorApprovedBy IS NOT NULL THEN 1
  END

Or a complicated CASE expression:

WHERE 1 =
  CASE 
    WHEN @UserRole = 'Analyst' AND SupervisorApprovedBy IS NULL THEN 1
    WHEN @UserRole = 'Analyst' THEN 0
    WHEN SupervisorApprovedBy IS NOT NULL THEN 1
  END

or with a bit more easy to understand AND / OR:

WHERE 
    @UserRole = 'Analyst'  AND SupervisorApprovedBy IS NULL 
 OR @UserRole <> 'Analyst' AND SupervisorApprovedBy IS NOT NULL 
 OR @UserRole IS NULL      AND SupervisorApprovedBy IS NOT NULL 

Another issue is that SupervisorApprovedBy = SupervisorApprovedBy (and the equivalent SupervisorApprovedBy IS NOT NULL I used above) will not give you "all data". The rows where SupervisorApprovedBy is null will not be returned. If you do want them all, the conditions should be all adjusted:

WHERE 1 =
  CASE 
    WHEN @UserRole = 'Analyst' THEN 
        CASE WHEN SupervisorApprovedBy IS NULL THEN 1 END
    ELSE 1
  END


WHERE 1 =
  CASE 
    WHEN @UserRole = 'Analyst' AND SupervisorApprovedBy IS NULL THEN 1
    WHEN @UserRole = 'Analyst' THEN 0
    ELSE 1
  END


WHERE 
    @UserRole = 'Analyst'  AND SupervisorApprovedBy IS NULL 
 OR @UserRole <> 'Analyst' 
 OR @UserRole IS NULL       
3

Simply WHEN <FieldOrVariable> IS NULL THEN <output>.

Or for the inverse: WHEN <FieldOrVariable> IS NOT NULL THEN <output>.

("WHEN NOT FieldOrVariable IS NULL THEN " would also work but the above reads better so is less likely to be misunderstood later)

The same is true for other conditional clauses like those relating to IF and ON.

You can check if a field or variable is equal to NULL because all comparisons to NULL return NULL (which in a CASE or IF predicate is taken as meaning false), so WHEN <FieldOrVariable> = NULL THEN <output> and WHEN <FieldOrVariable> <> NULL THEN <output> will never match. NULL is essentially "unknown" so if you ask IF @value = NULL you are asking "is @value equal to something unknown" to which the only answer is "don't know". Even IF NULL = NULL PRINT 'NULL is NULL' will not work because it is asking "is something unknown equal to something else unknown", which is why we have the extra syntax IS NULL for dealing with circumstances where we need to know if a value is currently unknown.

  • That doesn't really fit into my code above – K09 Aug 1 '17 at 9:13
3

How about this. Obviously replace 'X' with whatever value wouldn't ever exist.

WHERE ISNULL(SupervisorApprovedBy,'X') = 
CASE 
    WHEN @UserRole = 'Analyst' THEN 'X'
    ELSE SupervisorApprovedBy
END

If you want it to return NULL's all the time.

WHERE ISNULL(SupervisorApprovedBy,'X') IN ( 
CASE 
    WHEN @UserRole = 'Analyst' THEN 'X'
    ELSE SupervisorApprovedBy
END, 'X')

Or as @ypercubeᵀᴹ suggested, as another ISNULL

WHERE ISNULL(SupervisorApprovedBy,'X') = 
CASE 
    WHEN @UserRole = 'Analyst' THEN 'X'
    ELSE ISNULL(SupervisorApprovedBy,'X')
END
  • 1
    Consider this before adding function on a search predicate tech-recipes.com/rx/55535/… – S4V1N Aug 1 '17 at 10:24
  • Valid point, without seeing the rest of the query or how the output is used or the expected result set to work with, its hard to say if its entirely appropriate or not. I would usually handle nulls in a CTE and filter on that instead – Ollie Aug 1 '17 at 10:36

Not the answer you're looking for? Browse other questions tagged or ask your own question.