2

I have execute only access to a stored procedure.

This SP seems to select some data from multiple tables, and returns one row. I need to store two columns of the output of this SP into a table.

Is there any way to do this within MySQL?

Marjeta
  • 1,111
  • 10
  • 26
  • Is this a stored procedure or a stored function? Stored procedures return output parameters and not rows. Stored functions can return rows however. – Cameron Tinker Jan 08 '14 at 20:34
  • 1
    Not exactly. *"MySQL supports a very useful extension that enables the use of regular SELECT statements (that is, without using cursors or local variables) inside a stored procedure. The result set of such a query is simply sent directly to the client."* http://dev.mysql.com/doc/refman/5.6/en/stored-routines-syntax.html Also, stored functions can only return one scalar value. – Michael - sqlbot Jan 08 '14 at 22:35
  • It's definitely a stored procedure and not a function. I have only execute permission, and I can not change it. – Marjeta Jan 10 '14 at 01:27

2 Answers2

1

If it returns a row, this is a stored function and not a stored procedure. You can use something like the following to insert into your table:

INSERT INTO tablename SELECT (SELECT col1, col2 FROM (SELECT somefunction()))

Otherwise, it will be a stored procedure and you should do something like this, assuming that @var1 and @var2 are output parameters:

CALL someprocedure(@var1, @var2, @var3)
INSERT INTO tablename SELECT(@var1, @var2)

See the documentation about Create Procedure and Create Function for more information about functions versus procedures.

Cameron Tinker
  • 9,634
  • 10
  • 46
  • 85
1

MySQL has an extension to stored procedures that allows the procedure to return one or more result sets to the client, as if the client had issued a SELECT query... but those results are ephemeral. They don't persist and they can't be stored in variables or otherwise accessed after the procedure finishes -- they can only be "fetched" the one time.

There is a way to make them accessible without breaking the way the procedure already works, as I discussed here, but you can't do it without a change to the procedure:

How to use Table output from stored MYSQL Procedure

The idea is for the procedure to write its output in a temporary table, and then return it to the caller by calling SELECT against the temporary table -- but to leave the temporary table behind so that the caller can access it directly if desired.

That's not exactly the same as what you're asking though, which is why I didn't mark this question as a duplicate, since you, unlike the other poster, do not appear to have administrative control of the procedure... but unless you can make the case for a change like this, there's not another way within MySQL to access those returned values, since they only exist in the result-set that's returned.

Of course, procedures do have optional OUT parameters, where you can hand variables to the procedure as part of arguments you use to call it, and it can set those variables, so that they'll have the values you need when the procedure is done, but that only works when the return values are scalars and would require a change to the procedure's interface, since procs in MySQL do not have "optional" arguments... if the procedure were changed to permit this, it would require an increased number of arguments to be provided every time it was called, and if other components are calling it, that could easily break other things.

Community
  • 1
  • 1
Michael - sqlbot
  • 169,571
  • 25
  • 353
  • 427
  • No, I do not have admin privileges over this stored procedure. I can't even see its definition. – Marjeta Jan 10 '14 at 01:35
  • That being the case, there is no way to capture those values on the server... you can only fetch them, and then send them back in another query. – Michael - sqlbot Jan 10 '14 at 01:39
  • When you say "fetch", do you mean fetch them in a perl script (or some other language that calls MySQL)? – Marjeta Jan 10 '14 at 01:42
  • Yes, however you're getting them now. In perl, for example, fetchrow_hashref() or fetchrow_arrayref() after prepare() and execute(). – Michael - sqlbot Jan 10 '14 at 02:14
  • Yes, I guess I'll have to do it in perl. I was just wondering if I could do it all within MySQl... Thanks anyway. – Marjeta Jan 10 '14 at 20:08