How to use Table output from stored MYSQL Procedure

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.

Here's a concrete example in MySql 8, based on @Michael-sqlbot's suggestion:

-- Create some test datadrop table if exists someData ;create table someData (itemId int, itemName varcharacter(20), itemQty int, itemCat int) ;insert into someData values(1,'First', 9, 1),(2,'Second',50, 3),(3,'Third', 12, 5),(4,'Fourth', 7, 3),(5,'Fifth', 1, 2),(6,'Sixth', 1, 1),(7,'Seventh', 9, 3),(8,'Eighth', 0, 2);-- Just checking that it's all there!select * from someData ;-- Define the procdelimiter //drop procedure if exists prcSomeData //create procedure prcSomeData()    comment 'Create a temporary table called "tmpSomeData"'begin    drop table if exists tmpSomeData ;    create temporary table tmpSomeData as        select itemCat            , sum(itemQty) as 'itemsTot'            , min(itemQty) as 'lowestQty'            , max(itemQty) as 'highestQty'            from someData            group by itemCat            order by itemCat        ;end //delimiter ;-- Gotta run the proc to instantiate the temp tablecall prcSomeData() ;        -- produces a temporary table "tmpSomeData", exists for the session-- Now it's usableselect * from tmpSomedata ;

Which produces:

itemCat|itemsTot|lowestQty|highestQty|-------|--------|---------|----------|      1|      10|        1|         9|      2|       1|        0|         1|      3|      66|        7|        50|      5|      12|       12|        12|

Even better solution is to create the temporary table outside of the stored procedure, and have the procedure just update/insert to it. This way results of multiple calls can be stored in one table (as long as there are the same columns inserted), and retrieved in the end with a simple SELECT.

Here is an example using the same data as #sinecospi above:

/* The data */drop table if exists someData ;create table someData (itemId int, itemName varchar(20), itemQty int, itemCat int) ;insert into someData values(1,'First', 9, 1),(2,'Second',50, 3),(3,'Third', 12, 5),(4,'Fourth', 7, 3),(5,'Fifth', 1, 2),(6,'Sixth', 1, 1),(7,'Seventh', 9, 3),(8,'Eighth', 0, 2);/* The stored procedure */DROP PROCEDURE IF EXISTS sproc;DELIMITER //CREATE PROCEDURE sproc(  IN fld CHAR(64),  IN tbl CHAR(64))BEGIN  SET @s = CONCAT('INSERT INTO results    SELECT \'',fld,'\' AS fld, x.* FROM (      SELECT ',fld,' AS bin, COUNT(',fld,') AS ct           FROM ',tbl,'           GROUP BY bin           ORDER BY bin) x;');  PREPARE stmt FROM @s;  EXECUTE stmt;  DEALLOCATE PREPARE stmt;  END //DELIMITER ;/* Calls */CREATE TEMPORARY TABLE IF NOT EXISTS results (  fld VARCHAR(30), bin VARCHAR(30), ct FLOAT);CALL sproc('itemQty','someData');CALL sproc('itemCat','someData');SELECT * FROM results;

In the end, the temporary table results has the output of 2 calls.