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?
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?
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.
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.
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
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.