Geek asks and answers

SELECT data from stored procedures

I have a complex SELECTquery that filters by time range, and I want this time range (start and end dates) to be specified using custom parameters. Therefore, I can use the stored procedure to do this, and return is a result set with multiple rows. The problem I am facing is how to deal with this result. I can not do something like:

SELECT * FROM (CALL stored_procedure(start_time, end_time))

although a stored procedure is just one SELECTthat takes parameters. The prepared server-side instruction also does not work (and they are also not permanent). Some suggest using temporary tables; the reason this is not an ideal solution is that 1) I don’t want to specify the table schema, and it seems that you need it, and 2) the lifetime of the temporary table is limited only by calling the request, this does not need to be insisted on.

So, to repeat, I want something like a persistent, ready-made instruction on the server side, whose return is a result set that MySQL can manipulate as if it were a subquery. Any ideas? Thank.

By the way, I am using MySQL 5.0. I know this is a pretty old version, but this function does not seem to exist in a later version. I'm not sure if a choice from a stored procedure is possible in other SQL engines; transition is not an option at the moment, but I would like to know if this is possible in any case if we decide to switch in the future.

+2
source share
1 answer

. , Oracle , . , . , , , :

select * from table(FunctionToBeCalls(parameters));

, , , .

MySQL . . , OUT INOUT , . . , .

+1

More articles:


All Articles