SQL Query with possible null parameters

Question

I have to do a query SQL to search into the table below

Person

Name - Surname - Age

using Name, Surname ad Age as parameter of my query.

Since this query is generated dinamically, may happen that one of this parameters is equal to "" or null. In this case I expect that the behavior obtained is:

• If Name = "", I want to search for surname and age regardless of the name (every name).

I have found a possible solution to this problem and is to use a LIKE statement, in this way:

SELECT * ...
WHERE Surname like '%%' AND Name like '%%' AND Age like '%%'

When I put %% in the like it returns me all records of the table. Is this correct or there is another way?

Answer 1

SQL offers methods for those issues. You can easily use (Surname IS NULL OR Name = '' OR ...)

See: https://dev.mysql.com/doc/refman/5.7/en/is-null-optimization.html

Theres also a thread with almost the same question: MySQL syntax checking if parameter is null

Answer 2

...
Where (IsNull(@SurName, '') = '' OR @SurName = t.SurName)
AND (IsNull(@Name, '') = '' OR @Name = t.Name)
AND (IsNull(@Age, '') = '' OR @Age = t.Age)

Answer 3

For sql server it could look something like this:

select *
from t
where (
    Surname like '%'+@Surname+'%'
    or coalesce(@Surname,'') = ''
    )
  and (
    Name like '%'+@Name+'%'
    or coalesce(@Name,'') = ''
    )
  and (
    Age like '%'+@Age+'%'
    or coalesce(@Age,'') = ''
    )

catch-all queries

• Dynamic Search Conditions - Erland Sommarskog
• Catch-all queries - Gail Shaw

Answer 4

SELECT * ...
WHERE 
IF(NAME IS NULL OR NAME='',Surname LIKE '%%' AND Age LIKE '%%', Surname LIKE '%%' AND NAME LIKE '%%' AND Age LIKE '%%');