Unable to Return Multiple Rows from a MySQL Function

shrileela — Tue, 11 Feb 2025 10:31:30 +0000

I am trying to create a MySQL function that returns multiple rows, but I am unable to achieve the expected result. Here’s what I attempted:

DELIMITER $$

CREATE FUNCTION FUN_GET_ORDERS_BY_CUSTOMER(p_customer_id INT)
RETURNS TABLE
AS

RETURN

SELECT 
order_id, 
order_date, 
total_amount 
FROM 
orders 
WHERE customer_id = p_customer_id;

DELIMITER ;

But this gives me a syntax error because MySQL functions don’t support returning tables directly like in some other databases.

What would be the best way to rewrite this function to return multiple rows?

Any guidance or best practices would be appreciated!

DEV Community: shrileela