MySQL Bugs: #31716: Cannot access results from a stored procedure from within a procedure/function

Bug #31716	Cannot access results from a stored procedure from within a procedure/function
Submitted:	19 Oct 2007 13:50	Modified:	30 Jul 2009 17:03
Reporter:	Andrew Hanna	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S4 (Feature request)
Version:	5.0.45	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	FUNCTION, query results, stored procedure

Description:
A stored procedure can return result sets.  It would be nice to be able to somehow access these results sets from either within another query or another stored procedure/function/view.

I realize that the procedure could potentially create more than one result set, but maybe there would be some way to overcome this.  Maybe result sets within the procedure could be named and accessed that way.  Or maybe just indexed by an array...  Or maybe you can just specifically return one result set, like in a function.

How to repeat:
-- basic query
SELECT * FROM (CALL procedure);

-- from with sp
DECLARE results CURSOR FOR SELECT * FROM (CALL procedure);
-- or
DECLARE results CURSOR FOR CALL procedure;
-- or
DECLARE results CURSOR FOR PROCEDURE procedure;

-- possible ways to handle multiple result sets
DECLARE results CURSOR FOR PROCEDURE procedure['my-resultset-name']
DECLARE results CURSOR FOR PROCEDURE procedure['my-other-resultset-name']
DECLARE results CURSOR FOR PROCEDURE procedure[0]

-- naming a result set in a procedure
CREATE PROCEDURE procedure ()
RETURN SELECT * FROM t;

CREATE PROCEDURE procedure ()
BEGIN
    SELECT PROCEDURE_RESULT `my-resultset-name` * FROM t;
    SELECT PROCEDURE_RESULT `my-other-resultset-name` * FROM t;
END||

Thank you for the bug feature request.

This would be a very big improvement for a future version.

This is a duplicate of Bug #10817 actually.