Bug #47458 SP resultset to be used in the FROM clause of a SELECT query
Submitted: 20 Sep 2009 11:37 Modified: 20 Sep 2009 16:38
Reporter: Meir Guttman Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: FROM clause, ST, stored procedures, stored programs

[20 Sep 2009 11:37] Meir Guttman 
Description:
Many stored procedures return a perfectly tabular structured resultsets. It would be a boon to use such a virtual table in a FROM clause of a SELECT query, much like the use of a sub-query there. Similarly, an alias (AS tbl_name) will be mandatory. If implemented, one could use the resultset in any of the JOIN types.
One obvious obstacle that I see is that the SP resultset doesn't provide "column" names and data types. So see my suggestions in the "suggested fix" part.
Such a feature will promote reuse of stored procedures, it will publish well the interface while hiding the internals.

How to repeat:
n/a

Suggested fix:
In addition to the IN | OUT | INOUT for the procedure's I/O list, add a new categury [b]COLUMN[/b]. Example:
[code]
CREATE PROCEDURE tbl_like (IN param1 data_type, IN ...., COLUMN col1 data_type AS `c1`, COLUMN col2 data_type AS `c2`, ...);
[/code]
[20 Sep 2009 16:21] Valeriy Kravchuk 
This is a duplicate of bug #10817, it seems.
[20 Sep 2009 16:38] Meir Guttman 
Dear Valeriy,
You are absolutely right! Sorry, I did try to serch the DB but this (and all the others...) didn't catch!
It is very sad however that 6 people asked for it in close to FOUR years and this is still pending.
Can you give us an indication of where does it stand?
Regards,
Meir