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?

Answer 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')

Answer 2

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

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