How do I access a result set from a stored procedure in another stored procedure?

Question

I have a non-trivial SELECT statement and I don't want to write it twice (standard SW development considerations). I want to use the results in two stored procedures. I am imagining something like this:

CREATE PROCEDURE consumerProcedureA()
BEGIN
 CALL supplierProcedure();
 -- ** insert magic here **
END;
CREATE PROCEDURE consumerProcedureB()
BEGIN
 CALL supplierProcedure();
 -- ** insert magic here **
END;
CREATE PROCEDURE supplierProcedure()
BEGIN
 SELECT field1, field2, field3, MESSYEXPR AS field4
 FROM (complicated sub-SQL query) -- blah blah blah
 ;
END;

Grant you, I could create a view to capture the common query, but is there a way to have a calling stored procedure access and process the result set returned by a called stored procedure?

Answer 1

I am looking for a way to do this myself (in a way that satisfies me..), but a perfectly valid way to do so is described in this Stack Overflow Q & A:

Calling a Stored Procedure in a Stored Procedure in MySQL

The accepted answer by AhamedMustafaM demonstrates a technique using OUT parameters with the following code:

 CREATE PROCEDURE innerproc(OUT param1 INT)
 BEGIN
 insert into sometable;
 SELECT LAST_INSERT_ID() into param1 ;
 END
 -----------------------------------
 CREATE PROCEDURE outerproc()
 BEGIN
 CALL innerproc(@a);
 // @a gives you the result of innerproc
 SELECT @a INTO variableinouterproc FROM dual;
 END

I'm sure not always the answer for what you are trying to accomplish, but it is certainly an option. While you couldn't get the direct result of what the procedure called, this would help you get individual values, possibly using multiple OUT parameters to get what you need.

• rich p: I was hoping for a way to gain access to a conceptual list to iterate through. This could be used as a key to a table whose relevant entries all have that key, I grant you - but that goes back to the early notes. I also grant that if I reduced my requirements to a fixed length tuple, your technique would be applicable. Thanks for the suggestion.
• Virus721: The title of the question clearly says "result SET"...
• Vérace: SELECT @a INTO variableinouterproc FROM dual; - correct me if I'm wrong, but isn't the DUAL table an Oracle construct?

Answer 2

you have to be a little tricky at this point.

first you create a temporary table for your select, this happens in the example in

CALL my_procedure(): 

Then you can use that temporary table and use it in your query, and finally you can DROP the temporary table.

Schema (MySQL v8.0)

CREATE TABLE example (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 fullname VARCHAR(100) NOT NULL,
 email VARCHAR(100) NOT NULL,
 something VARCHAR(255) NOT NULL
);
INSERT INTO example (id, fullname, email, something) VALUES
 (1, 'Name 1', '[email protected]', 'Something 1'),
 (2, 'Name 2', '', 'Something 2'),
 (3, 'Name 1', '[email protected]', 'Something 3'),
 (4, 'Name 3', '[email protected]', 'Something 4'),
 (5, 'Name 3', '[email protected]', 'Something 5'),
 (6, 'Name 4', '', 'Something 6');
 
delimiter $$
CREATE PROCEDURE my_procedure ()
BEGIN
 CREATE TEMPORARY TABLE new_tbl 
 SELECT MAX(fullname), email 
 FROM example 
 GROUP BY email ;
END $$
delimiter ;
delimiter $$
CREATE PROCEDURE my_procedure2 ()
BEGIN
 SELECT * FROM new_tbl;
 
 END $$
delimiter ;
delimiter $$
CREATE PROCEDURE my_procedure3 ()
BEGIN
 SELECT * FROM new_tbl;
 #DROP TEMPORARY TABLE IF EXISTS new_tbl;
 END $$
delimiter ;

---

Query #1

CALL my_procedure();

There are no results to be displayed.

---

Query #2

call my_procedure2();
| MAX(fullname) | email |
| ------------- | ----------------- |
| Name 1 | [email protected] |
| Name 4 | |
| Name 3 | [email protected] |

---

Query #3

call my_procedure3();
| MAX(fullname) | email |
| ------------- | ----------------- |
| Name 1 | [email protected] |
| Name 4 | |
| Name 3 | [email protected] |

---

View on DB Fiddle

• Starx: Thanks for the workaround. Although not perfect, I guess this will do.
• Starx: What is the difference between procedure 2 and 3?
• nbk: there is no difference it only shows that you can use the temporary table for more than one procedure