MySQL: How to get the results of a stored procedure inside a stored function?
I have a stored procedure that makes very complex joins and returns 1 row of data (LIMIT 1 is used). In my application, I use this data to perform some calculations. This way I am trying to keep business logic out of stored procedures. But now I need to get this data in another stored function in order to do some calculations. The reason I want to create a stored function is because it will be called in another query that returns thousands of rows.
Is it possible? I don't want to duplicate the complicated join logic in the stored function ... The only solution I see is to use out parameters.
-----------------------------------
PS. I decided to explain my situation to you, maybe you can offer me another solution.
I need to calculate the shipping cost paid (this is the final price including packing, shipping and customs clearance) of the goods. The calculation of this billable shipping cost is a bit tricky and I don't want to keep the complicated logic in my stored procedures and functions. So I created a stored procedure that fetches all the data needed to calculate the price, and I use that data in my application to calculate the price. So far everything has been working well.
But now I need to create a price list with shipping charges and we have thousands of items. So if I name my stored procedure for each product world, it takes thousands of rounds (requests) to the server. This is why I want to create a function that will call the stored procedure and calculate the price based on the returned data. And then I want to use it like this:
SELECT Description, blablabla, Weight, ..., GetDeliveryDutyPaidPrice (...) FROM the price list;
Any ideas?
a source to share
Instead of a stored procedure, do this kind of. This should solve your problems.
EDIT:
To be precise, what you want to do is create a view that contains all the columns that you use in your calculations. A view is similar to a stored procedure in that it allows the underlying database engine to optimize its performance, but it can be queried just like a regular table using the WHERE clause. So your base implementation of your tables can only contain the columns that each table needs (thus simplifying your schema), but the view can compose all the different columns from different tables into one "table view" which can be queried using the clause WHERE. In this way,you can take advantage of stored procedures (by optimizing your SQL connections for commonly used joins) and be able to filter the results appropriately (using the WHERE clause) while keeping your logic from your database.
Really. Views are a very very good thing. Use them.
a source to share