Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to use Table output from stored MYSQL Procedure

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.

like image 329
Johannes Avatar asked Jul 30 '13 10:07

Johannes


People also ask

Can a stored procedure return a table MySQL?

You cannot return table from MySQL function. The function can return string, integer, char etc. To return table from MySQL, use stored procedure, not function.

How do I call a procedure with parameters in MySQL?

This procedure accepts id of the customer as IN parameter and returns product name (String), customer name (String) and, price (int) values as OUT parameters from the sales table. To call the procedure with parameters pass @parameter_name as parameters, in these parameters the output values are stored.


1 Answers

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.

like image 107
Michael - sqlbot Avatar answered Sep 21 '22 06:09

Michael - sqlbot