MySql stored procedures: How to select from procedure table?

Question

Hi!

Let's say we have a stored procedure selecting something from a table:

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

How can I use the result from this procedure in a later select? (I've tried

SELECT * FROM (CALL database.getExamples())

but with no success.) Should I use SELECT... INTO outVariable in the procedure? Or should I use a function returning the table instead?

Answer 1

If you want to re-use the 'procedure' then, yes, I would put it into a table valued function.

In SQL server you can then do SELECT * FROM database.getExamples()

Otherwise you could just SELECT INTO a #temporary table inside the stored procedure.

Answer 2

CREATE TABLE #TempTable
(OID int IDENTITY (1,1),
VAr1 varchar(128) NOT NULL,
VAr2 varchar(128) NOT NULL)

Populate temporary table

INSERT INTO #TempTable(VAr1 , VAr2 )
SELECT * FROM examples

Answer 3

Reformulated the question in this thread: http://stackoverflow.com/questions/1637288/mysql-can-a-stored-procedure-function-return-a-table. Obviously, it isn't possible without the use for temp tables.