MySql stored procedures: How to select from procedure table?

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?

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Reformulated the question in this thread: Can a stored procedure/function return a table?.
Obviously, it isn’t possible without the use for temp tables.

Method 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

Method 3

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

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

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating