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

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.

Answer 2

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 3

I have a stored proc A, which creates a temporary table. Inside this stored proc, I have another stored proc B where I am inserting the values into the temp table. After the completion of stored proc B, I am doing a select *from #temptable. Since I have created the temp table in proc A, I shouldn't have any problems while doing a select query. Am I correct here??