Blank input Parameters in a WHERE condition of a SELECT statement

Issue

I have a report with 10 input parameters wherein only two are obligatory, the rest are optional. The problem is, as far as I know, if you use an input parameter that is blank in a WHERE condition of a SELECT statement no records will be selected from a table, so how to fix that ?

lets try something simple:

SELECT * FROM contrat WHERE article = 'sumsung' AND price = null

the user entered an article and did not enter a price
so the query will return nothing because price is null even when we have a contract with a sumsung article

Solution

If by blank you mean null or an empty string, you must construct for each of the optional parameters a condition like:

column_name = :optional_param OR :optional_param IS NULL

or:

column_name = :optional_param OR :optional_param = ''

So, your WHERE clause should look like:

WHERE <conditions for the non null parameters>
  AND (column_name1 = :optional_param1 OR :optional_param1 IS NULL) 
  AND (column_name2 = :optional_param2 OR :optional_param2 IS NULL)
  AND ....... 

Answered By – forpas

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published