1

I'm attempting to use a stored procedure to search on a join of several tables and having some trouble handling parameters in the 'WHERE' clause that may or may not be null. Here is a simplified version of the join.

SELECT 
c.id, 
c.name, 
c.title, 
d.city,
d.state
e.license
FROM customer AS c
LEFT JOIN address AS d on c.id = d.c_id
LEFT JOIN license AS e on c.id = e.c_id

Not all customers will have addresses. Not all customers will have licenses. I would like to pass parameters for each field that will be disregarded if null and observed if not.

I've been advised that using a WHERE(param1 IS NULL or param1 = c.id) is going to perform poorly for large data sets and to use dynamic sql instead. Is this the best way to approach the problem, and if so, could anyone advise on the syntax for dynamic SQL with multiple potentially null parameters specific to MariaDB?

rlacos1
  • 13
  • 2
  • There are other questions about dynamic SQL in MySQL/Mariadb. e.g. https://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure. The answers might help. – Nikkorian Feb 17 '22 at 10:54

0 Answers0