Use results of a stored procedure in a select statement [MySql]

Question

I have a stored procedure which returns several values (1 row). I need to use the results of that stored procedure in a select query.

Here is pseudo-code of how I would like it to be (such syntax does not exist, but let's pretend it would allow to save 3 values, which my stored procedure returns, into C, D and E):

SELECT
  t1.A, t1.B,
  MyStoredProc(t1.A) AS (C, D, E)
FROM
  t1
ORDER BY
  D
LIMIT
  1

(on the client side I need to get A, B, C, D and E)

I can rewrite the stored procedure to a stored function which returns values as a concatenated string with delimiters and parse out the values in the query, but I would like to find a cleaner solution.

Why not call your stored function three times, with different parameters? — eggyal
– eggyal, Commented Oct 27, 2013 at 21:41
SELECT t1.A, t1.B, myStoredFunc(t1.A, 1) AS C, myStoredFunc(t1.A, 2) AS D, myStoredFunc(t1.A, 3) AS E FROM t1 ORDER BY D LIMIT 1 — eggyal
– eggyal, Commented Oct 27, 2013 at 22:02
My stored procedure/function executes a complex SELECT query inside, so it's not good to call it 3 times. As I wrote in the question in question, the stored function could return the values as a string with delimiters, but then I would need to parse it using SQL, and I want a cleaner solution, though I don't think one exists (in mysql). — nightcoder
– nightcoder, Commented Oct 27, 2013 at 22:16

Community · Accepted Answer · 2017-05-23 11:57:21Z

3

You can't access the values from a result-set returned by a stored procedure within another query. They are only sent to the client. The result-set looks like a table, but it is not a table.

There is, however, a workaround, described here:

How to use Table output from stored MYSQL Procedure

It doesn't get you precisely where you need to go, but it gets you as close as you can get.

Otherwise, you can write three stored functions all run the exact same bit of logic, but which cache their results in session variables so whichever function executes first will set things up for the other two so they complicated logic only executes once for a given input value and then re-executes for the next input value since it's different than the one cached.

IF NOT @__cached__function_input <=> input_arg THEN
  SET @__cached__function_input = input_arg;
  SELECT complicated, logic, things INTO @__cached__a, @__cached__b, @__cached__c;
END IF;

RETURN @__cached__a; # in function "a" -- return b in b and c in c

If you use unique variable names for any given function, these won't collide. Functions are executed serially, not in parallel, even for data on the same row of a statement that returns multiple rows.

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered Oct 29, 2013 at 4:53

Michael - sqlbot

181k28 gold badges388 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nightcoder Over a year ago

Michael, in my case to use the temporary table option I will first have to get all t1.A values which my query (see the question) returns, then I will have to call the stored procedure for each t1.A value and fill the temporary table, and then I will have to execute the final query with a join to the temporary table. Not very elegant.

Collectives™ on Stack Overflow

Use results of a stored procedure in a select statement [MySql]

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related