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
CodePudding user response:
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.
CodePudding user response:
SELECT *
FROM WorkOrders
WHERE @SelectedFacilityCode in ('Any', FacilityCode)