MySQL: How to get results of stored procedure inside a stored function?

Question

Hello,

I have a stored procedure that makes very complex joins and returns 1 row of data (LIMIT 1 is used). In my application I then use that data to make some calculations. This way I try to keep business logic out of stored procedures. But now I need to get that data in another stored function to also make some calculations. The reason I want to create the stored function is because it will be called in another query that returs thousands of rows.

Is it possible? I don't want to duplicate complex join logic in the stored function... The only solution I see is to use output parameters.

-----------------------------------
PS. I decided to explain you my situation, maybe you will offer me another decision.

I need to calculate delivery duty paid price (it's final price, including packaging, delivering and customs clearance) of the goods. The calculation of this delivery duty paid price is a bit complex, and I don't want to keep complex logic in my stored procedures and functions. So I created a stored procedure that selects all the data necessary to make the calculation of the price and I use that data in my application to calculate the price. For now everything works good.

But now I need to create a price-list with delivery duty paid prices and we have thousands of goods. So if I call my stored procedure for every peace of goods it will take thousands of round-trips (queries) to the server. That is why I want to create a function that will call the stored procedure and calculate the price based on returned data. And then I want to use it like this:
SELECT Description, blablabla, Weight, ..., GetDeliveryDutyPaidPrice(...) FROM pricelist;

Any ideas?

Answer 1

Instead of a stored procedure, make it a view. That should solve your problems.

EDIT:

To be precise, what you want to do is create a view which contains all of the columns which you use in your calculations. A view is similar to a stored procedure in that it allows the underlying DB engine to optimize for its performance, yet it can be queried the same way that a normal table can, using a WHERE clause. So your underlying implementation of your tables can have only the columns that each table needs (thereby making your schema simple), but the view can compose all of the different columns from the different tables into one single "table view" which can be queried using the WHERE clause. In this way, you can gain the benefits of stored procedures (optimizing your SQL joins for commonly used joins) and be able to filter your results appropriately (using the WHERE clause) while still keeping your logic out of your database.

Really. Views are a very very very good thing. Use them.