I am trying to use the result of a stored function in a WHERE statement in MySQL (5.x), but it fails because in the function I am selecting values from a table into an INT variable and then returning them/it, which obviously doesn't work if the SELECT returns more than 1 row. I've tried returning a TABLE (as I understood TABLE means array in MySQL) but that didn't work either.
Is there any way that I could do something like:
SELECT ID FROM myTable WHERE ID IN my_function(params);
Thank you.
This cannot be done...
First, you cannot use a stored function to return multiple results - you would need to use a stored procedure.
The MySQL docs state:
Statements that return a result set can be used within a stored procedure but not within a stored function.
Second, you cannot use a stored procedure in a query - see this SO question.
Have you considered using 'HAVING ...' at the end of your query?
CREATE PROCEDURE my_proc(IN param INT, OUT result TABLE) which doesn't work also, it fails at the TABLE keyword
SELECT * FROM cities WHERE id IN (CALL isLessThan(5))