CODEWITHSUNDEEP
mysqlstored-procedures
Cambiata
Cambiata

Reputation: 3845

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?

Upvotes: 31

Views: 98179

Answers (3)

RR.
RR.

Reputation: 677

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.

Upvotes: -5

Cambiata
Cambiata

Reputation: 3845

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.

Upvotes: 7

Dani
Dani

Reputation: 15069

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

Upvotes: 3

Related Questions