MySQL How to INSERT INTO [temp table] FROM [Stored Procedure]

Question

This is very similar to question 653714, but for MySQL instead of SQL Server.

Basically, I have a complicated select that is the basis for several stored procedures. I would like to share the code across the stored procedures, however, I'm not sure how to do this. One way I could do this is by making the shared select a stored procedure and then calling that stored procedure from the other ones. I can't figure out how to work with the result set of the nested stored procedure. If I could put them in a temp table I could use the results effectively, but I can't figure out how to get them in a temp table. For example, this does not work:

CREATE TEMPORARY TABLE tmp EXEC nested_sp();

Answer 1

The problem is, Stored Procedures don't really return output directly. They can execute select statements inside the script, but have no return value.

MySQL calls stored procedures via CALL StoredProcedureName(); And you cannot direct that output to anything, as they don't return anything (unlike a function).

MySQL Call Command

Answer 2

You cannot "SELECT INTO" with stored procedures.

Create the temporary table first and have your stored procedure to store the query result into the created temporary table using normal "INSERT INTO". The temporary table is visible as long as you drop it or until the connection is closed.

Answer 3

My first reaction was "That sounds like a view to me". Doesn't that abstract it enough so you can just add the variability into an SP per case?

Anything that adds a temp table that wouldn't otherwise be there is a very likely antipattern.