Using Case Statement in SQL with parameter/variable to check for Null values

Question

I am trying to write a SQL Select statement to return records based on a user input through a front end. I want to write the Select statement like this:

SELECT somefields
FROM sometable
WHERE CASE variable
         WHEN 'blank' THEN field IS NULL
         ELSE field = field
      END

Basically I either want to filter a column to find NULL values or ignore the filter and return all values depending on the value of the variable. I know that the results of the CASE statement is not executable but how can I do this?

Solution 1

When variable is 'blank', the following query will give you rows where field is NULL. When variable is anything else, it will give you all rows:

SELECT somefields
FROM sometable
WHERE
    (variable = 'blank' AND field IS NULL)
    OR (variable <> 'blank')

Solution 2

The following snippet should behave as follows:

• when @variable is null, return all rows
• when @variable = 'blank', return all rows where field is null or field = 'blank'
• otherwise, return rows where @variable equals field

Code snippet:

WHERE 1 = CASE
      WHEN @variable is null then 1
      WHEN @variable = 'blank' and field is null then 1
      WHEN @variable = field then 1
      END

Solution 3

You can use NULLIF() (link is for SQL Server, but NULLIF() should be standard):

SELECT somefields
  FROM sometable
 WHERE field = NULLIF(variable, 'blank')

Solution 4

SELECT somefields
FROM sometable
WHERE ((variable IS NULL OR variable = 0) OR (variable = field))

WHERE Criteria is apply when variable have value
For Example:

DECLARE @CityName VARCHAR(50)
SET @CityName = NULL

SELECT CityName
FROM City
WHERE ((@CityName IS NULL ) OR (@CityName = CityName ))

When City is null then tables return all rows

View more solutions