MySQL Search Query Using LIKE

Question

I'm trying to build a query that takes some input parameters. Few things to note...

1. Not all parameters are required to be passed to the query.
2. If a parameter is not going to be passed to the query, must it still be passed anyway but as NULL?
3. If a parameter is not passed, how would I build it to not include the parameter?

So say you have...

IN name VARCHAR(30),
IN age VARCHAR(2),
IN address VARCHAR(50)

And you only want to search by name...

SELECT * FROM Table WHERE 
(
NAME LIKE CONCAT(name, '%')
);

This doesn't seem to work because the age wasn't passed in. Or what if the name was passed in and the address but not the age? How would I build that?

Answer 1

As a previous poster state, pass NULL for the unused parameters. Then structure your query similar to...

SELECT * FROM Table WHERE 
(
  ((nameParameter IS NULL) OR (NameColumn LIKE '%' + NameParameter + '%'))
  AND 
  ((ageParameter IS NULL) OR (AgeColumn = ageParameter))
  AND 
  ((addressParameter IS NULL) OR (AddressColumn = addressParameter))
)

Answer 2

The issue is optinal parameters Cannot be used in MYSQL.

The workaround is to pass null value for parameter and inside the stored procedure use IF statement to handle the different situations.

Take a look here:

Writing optional parameters within stored procedures in MySQL?

Answer 3

Try this:

SELECT * FROM Table
WHERE name LIKE '%nametofind%';