select - How to use Table output from stored MYSQL Procedure

Question

I've been looking for the last hour or so and haven't found a conclusive answer to this seemingly simple problem:

How do you call a stored MYSQL function/procedure and use its output in further SELECT queries?

---

Although this obviously doesn't work, this is the kind of thing I'd like to have:

SELECT P.`id` FROM (CALL test_proc()) AS P

Where test_proc() is defined by:

DROP PROCEDURE IF EXISTS test_proc;
DELIMITER ;;
CREATE PROCEDURE test_proc()
BEGIN
    SELECT * FROM `table`;
END;;
DELIMITER ;

Just as an example. I'd be fine with using a stored function as well.

Answer 1

This can't be done, directly, because the output of an unbounded select in a stored procedure is a result set sent to the client, but not technically a table.

The workaround is to let the proc put the data in a temporary table after creating the table for you. This table will be available only to your connection when the procedure finishes. It will not cause a conflict if somebody else runs the proc at the same time and won't be visible to any other connection.

Add this to the procedure:

DROP TEMPORARY TABLE IF EXISTS foo;
CREATE TEMPORARY TABLE foo SELECT ... your existing select query here ...;

When your procedure finishes, SELECT * FROM foo; will give you what you what you would have gotten from the proc. You can join to it pretty much like any table.

When you're done, drop it, or it will go away on its own when you disconnect. If you run the proc again, it will be dropped and recreated.