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.

Plagiarize

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.

Chemotherapy

How can I call test_proc() while creating temporary table. I want to keep the logic in SP separate and do not want to mix select statements with temp table – Duplication

@HimalayaGarg please explain what you mean "while creating" -- do you mean something like CREATE TEMPORARY TABLE t1 USING RESULT FROM CALL test_proc()? Sorry, I just made that up. There's no such syntax. Please explain what you're trying to do. – Chemotherapy

@Michael-sqlbot Yes, you got it right. I want something similar as you wrote above. Is there some way we can create temporary table by calling SP. I want to use the results of SP (I can not change SP as it is in another DB, not administered by me) – Duplication

No, that isn't possible. When a stored procedure "returns" a result-set that looks like a table, this result actually bypasses some of the internals of MySQL Server and the result is simply written to the connected client. That's why you can't do things like CALL test_proc() WHERE ... LIKE ... LIMIT ... etc. You can only capture this output from a client connection and then write some Perl/Python/PHP/Ruby/whatever to push it right back to the server by creating insert statements to insert the retrieved values into a temp table you created. – Chemotherapy

Answer 2

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

-- Create some test data
drop 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 proc
delimiter //
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 table
call prcSomeData() ;        -- produces a temporary table "tmpSomeData", exists for the session
-- Now it's usable
select * from tmpSomedata ;

Which produces:

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

Hamid

Answer 3

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.

Nalepka