Search code examples
sqlsql-servercasewhere-clause

Selecting null and non-null values in a case expression

I want to return all the work orders where the FacilityCode equals @SelectedFacilityCode except where it equals 'Any'. Then I want it to return all work orders including work orders where FacilityCode is null. The problem is when FacilityCode = FacilityCode, it only returns all the work orders where FacilityCode isn't null. How do I return both null and non-null values when @SelectedFacilityCode = 'Any'?

My actual query is more complex than this but this is the crux of the problem. I'm using SQL Server if that helps.

Thanks!

SELECT * FROM WorkOrders
WHERE FacilityCode = CASE
  WHEN @SelectedFacilityCode = 'Any' THEN FacilityCode
  ELSE @SelectedFacilityCode
END
Solution

  • CASE is an expression not a statement i.e. it can only return a value not an expression. You want logical operators (AND/OR) e.g.

    SELECT *
FROM WorkOrders
WHERE @SelectedFacilityCode = 'Any'
OR FacilityCode = @SelectedFacilityCode

    I highly recommend learning the use of logical operators in SQL because they are a core building block for writing queries. Including the use of parentheses to ensure correct ordering.