MySQL: Use stored procedure in SELECT statements or similar

Dear all,
currently, I have programmed a stored procedure that receives one argument, the id of a product, and returns 1 row x 5 columns of data for that product via an unbounded SELECT statement. Think of it as a procedure generating a "Sales Report" for a given product.

Now, I'd like to be able, by modification of that procedure or by other means, to have this Sales Report generated for many product ids that I would query for with a SELECT statement. For example, generate a table Sales Reports for all non-food merchandise or products with a sales price below 100$.
I'd like to be able to work flexibly with that stored procedure and be able to use it with SELECT statements, or similarly to achieve similar results.

I'm curious about your suggestions.
If there are further questions, I'll be happy to clarify.
LVL 3
robofixAsked:
Who is Participating?
 
robofixAuthor Commented:
After some of my own research I can say that what i was looking for can't be done.
a stored procedure cannot be included in a SELECT statement.

The SELECT query must rather be included in the stored procedure code; however, this reduces flexibility.
0
 
rajvjaCommented:
select * from products prod
inner join proddetails det on prod.prodid=det.prodid
where prod.salesprice < 100
0
 
robofixAuthor Commented:
hi rajvja,
what about my sales_report procedure that I need applied for every prodid?
0
 
rajvjaCommented:
select * from products prod
inner join proddetails det on prod.prodid=det.prodid
where prod.salesprice < 100
and prod.prodid = @prodid
0
 
robofixAuthor Commented:
i don't think this is correct.
Further suggestions anyone?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.