SELECT-ing data from stored procedures

I have a complicated SELECT query that filters on a time range, and I want this time rang开发者_C百科e (start and end dates) to be specifiable using user-supplied parameters. So I can use a stored procedure to do this, and the return is a multiple-row result set. The problem I'm having is how to deal with this result set afterwards. I can't do something like:

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

even though the stored procedure is just a SELECT that takes parameters. Server-side prepared statement also don't work (and they're not persistent either). Some suggest using temporary tables; the reason that's not an ideal solution is that 1) I don't want to specify the table schema and it seems that you have to, and 2) the lifetime of the temporary table would only be limited to a invocation of the query, it doesn't need to persist beyond that.

So to recap, I want something like a persistent prepared statement server-side, whose return is a result set that MySQL can manipulate as if it was a subquery. Any ideas? Thanks.

By the way, I'm using MySQL 5.0. I know it's a pretty old version, but this feature doesn't seem to exist in any more recent version. I'm not sure whether SELECT-ing from a stored procedure is possible in other SQL engines; switching is not an option at the moment, but I'd like to know whether it's possible anyway, in case we decide to switch in the future.

Selecting from functions is possible in other engines. For instance, Oracle allows you to write a function that returns a table of user defined type. You can define result sets in the function, fill them by using queries or even using a combination of selects and code. Eventually, the result set can be returned from the function, and you can continue to query on that by using:

select * from table(FunctionToBeCalls(parameters));

The only disadvantage, is that this result set is not indexed, so it might be slow if the function is used within a complex query.

In MySQL nothing like this is possible. There is no way to use a result set from a procedure directly in a select query. You can return single values from a function and you can use OUT or INOUT parameters to you procedure to return values from. But entire result sets is not possible. Filling a temporary table within you procedure is the closest you will get.