CODEWITHSUNDEEP
mysqlsqldatabasestored-procedures
nightcoder
nightcoder

Reputation: 13527

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

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.

Upvotes: 3

Views: 11168

Answers (1)

Michael - sqlbot
Michael - sqlbot

Reputation: 179432

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.

Upvotes: 3

Related Questions