The question is published on by Tutorial Guruji team.
I trying to learn MySQL stored procedures, and I’m stuck in a If..Then matter. I’m trying to figure out how to show columns do to the matter of a result from the IF.
Here is my code, hope you understand what i’m trying to do, my issue is between “…” and “…” :
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_helloworld`() BEGIN SELECT ... IF pt.is_part_payment = 1 THEN pp.due_date AS due_date, pp.amount AS amount ELSE o.due_date AS due_date, o.amount AS amount END IF; ... FROM orders o INNER JOIN payment_types pt ON pt.id = o.payment_type_id INNER JOIN part_payments pp ON pp.order_id = o.order_id WHERE o.active = 1 END
As you’ve seen, you can’t place an
if statement in the middle of an SQL query. You could, however, use a
case expression, although you’d have to do it for each field you’re querying separately:
SELECT ... CASE pt.is_part_payment WHEN 1 THEN pp.due_date ELSE o.due_date END AS due_date, CASE pt.is_part_payment WHEN 1 THEN pp.amount ELSE o.amount END AS amount, ... FROM orders o INNER JOIN payment_types pt ON pt.id = o.payment_type_id INNER JOIN part_payments pp ON pp.order_id = o.order_id WHERE o.active = 1