CODEWITHSUNDEEP
mysqlstored-procedures
Marjeta
Marjeta

Reputation: 1141

save stored procedure output into a table

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?

Upvotes: 2

Views: 7731

Answers (2)

Michael - sqlbot
Michael - sqlbot

Reputation: 179154

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.

Upvotes: 1

Cameron Tinker
Cameron Tinker

Reputation: 9789

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.

Upvotes: 1

Related Questions