MySql: Can a stored procedure/function return a table?

Question

Hi!

Can a MySql stored procedure/function return a table without the use of temp table?

Creating the following procedure

CREATE PROCEDURE database.getExamples() 
SELECT * FROM examples;

and later calling it with

CALL database.getExamples()

displays the example table - just as expected - but the following doesn't seem to be possible:

SELECT * FROM CALL database.getExamples()

Is it possible at all to return a qurey result table from a stored procedure/function, and if so - how? Regards / Jonas

Answer 1

As for now, this is not possible.

Here is the documentation on what may be used in the FROM clause:

table_references:
    table_reference [, table_reference] ...

table_reference:
    table_factor
  | join_table

table_factor:
    tbl_name [[AS] alias] [index_hint)]
  | table_subquery [AS] alias
  | ( table_references )
  | { OJ table_reference LEFT OUTER JOIN table_reference
        ON conditional_expr }

join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON conditional_expr
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor

join_condition:
    ON conditional_expr
  | USING (column_list)

index_hint:
    USE {INDEX|KEY} [FOR JOIN] (index_list)
  | IGNORE {INDEX|KEY} [FOR JOIN] (index_list)
  | FORCE {INDEX|KEY} [FOR JOIN] (index_list)

index_list:
    index_name [, index_name] ...

As you can see, stored procedures are not in this list.

Answer 2

You might be able to do what you are attempting by using a view instead of a stored procedure, but that entirely depends on what the stored procedure does.

If using a temp table is your only option, consider using the MEMORY storage engine.