Dynamic MySQL Where Clause in Stored Procedure

Question

I have a question and maybe its simple (for you Gurus).

I'm transposing my SQL Paging class from C# to a MySQL Stored Procedure. In my C# home-made object, the query is dynamically built based off a criteria. Example:

if(keywords is not null)
{ 
  whereClause += "WHERE description LIKE '%keywords%'"
}
if(price is not null)
{
  whereClause += "AND price = '%price%'"
}

....

string query = "SELECT col1, col2 FROM tblThreads " + whereClause

Now, my question is: How do I do a dynamic where clause in MySQL similar to this? Or rather, if they don't enter anything for those parameters, how would I tell MySQL in the Stored Procedure to skip those? IE:

SELECT col1, col2 FROM tblThreads

Would something like this work, if those parameters were null?

SELECT col1, col2 FROM tblThreads WHERE (IS NULL @keywords OR description like '%@keywords%'

??

Thanks guys.

Histo

Answer 1

The easiest way if you're allowing them to query the entire database is to just add a 1 = 1 to your statement Something like

whereClause = "WHERE 1 = 1"

if(keywords is not null)
{ 
 whereClause += "AND description LIKE '%keywords%'"
}
if(price is not null)
{
 whereClause += "AND price = '%price%'"
}

Weaks

I like this one. I use this myself, for when I have to do "heavy" parsing of SQL statements, ie programmatically removing AND clauses. Allows you to 'go back to previous "AND ", and remove that line, etc. Discounting the WHERE clause makes this much easier. – Hijoung

I saw this once before, and even though it doesn't answer my original question it is interesting. Assuming I stick to my C# object class, right now I'm trying to identify if I'm using "AND" (I have a boolean variable that is set to true the moment I use AND.) Assuming I do this, I wouldn't have to keep track of when I used my first parameter, correct? Does this have any performance penalties and does 1 = 1 cause any issues? – Histo

Just looked up some additional details here: https://mcmap.net/q/87196/-quot-where-1-1-quot-statement-duplicate I actually like this idea and will try implementing it. Thanks for the tip! I may also be able to finally build my stored procedure using this method as well if I decide to pursue that avenue. Thanks guys. – Histo

Answer 2

You can use CASE statement to check for the value of @keywords, eg.

SELECT  col1, col2 
FROM    tblThreads 
WHERE   description LIKE  CASE WHEN @keywords IS NULL 
                            THEN description
                            ELSE CONCAT('%', @keywords, '%')
                            END
        AND
        price LIKE  CASE WHEN @price IS NULL 
                            THEN price
                            ELSE CONCAT('%', @price, '%')
                            END

Chaplet

What happens if all the parameters are null? Wouldn't that make for poor execution? IE: description LIKE '%%' AND price LIKE '%%' AND param3 LIKE '%%' AND ... paramX LIKE '%%'? – Histo

Ignore my last comment. If I do the 1 = 1 method, then insert a case statement afterwords with a CASE WHEN @price <> NULL THEN AND price LIKE '%price%' Should work I'm guessing... I've never tried it, so this is speculation. – Histo

Answer 3

SELECT col1, col2 
FROM tblThreads 
WHERE case when @keywords is not null then description like '%' + @keywords + '%' when @price is not null then price like'%' + @price + '%' end 

Lombardy

Answer 4

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_Name4`(
 IN a int(255),
 IN b int(255),
 IN dept_id_in int(255)
)
BEGIN
     SELECT
        emp.emp_id, emp.emp_name,
        emp.emp_job, 
        emp.dept, 
        emp.percent_doctor,
        emp.percent_center,
        patient_exam.exam_id,
        patient_exam.p_id,
        ABS(SUM(patient_exam.exam_price)) as SUM_price,
        ABS((SUM(patient_exam.exam_price)*  emp.percent_center )/100   ) as total_doc,
        ABS((SUM(patient_exam.exam_price)*  emp.percent_doctor )/100   ) as total_center
      FROM emp
      left join patient_exam 
      ON emp.emp_id = patient_exam.doctor
      WHERE emp.emp_is_delete=0 and patient_exam.ex_is_delete=0 and 1=1 

CASE WHEN dept_id_in IS not NULL 
                        THEN
                        and patient_exam.dept_id=dept_id_in

                        END   

                        group by emp.emp_id, patient_exam.exam_id order by emp.emp_id, patient_exam.exam_id desc limit a,b;

        END$$
DELIMITER ;

Procession