Blank input Parameters in a WHERE condition of a SELECT statement

user_15556565 0
Viewed 177

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

where-clause mysql sql
1 Answers

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 .......
user_104988280
Related