i want to make MySQL stored procedure with dynamic clause where

Question

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 ;

It's worth considering writing a `VIEW` instead. This can't be extended in the same way a `VIEW` can. — tadman, Apr 08 '20 at 00:42
https://stackoverflow.com/a/697685/1880170 already answered in this link. — Guilherme Muniz, Apr 08 '20 at 11:32

score 0 · Accepted Answer · answered Apr 07 '20 at 23:47

I suspect that you want some boolean logic rather than a case expression:

WHERE 
    emp.emp_is_delete = 0 
    AND patient_exam.ex_is_delete = 0 
    AND (dept_id_in IS NULL OR patient_exam.dept_id = dept_id_in)

You can also express this with COALESCE():

WHERE 
    emp.emp_is_delete = 0 
    AND patient_exam.ex_is_delete = 0 
    AND patient_exam.dept_id = COALESCE(dept_id_in, patient_exam.dept_id)

i want to make MySQL stored procedure with dynamic clause where

1 Answers1