Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

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.
0
robofix
Asked:
robofix
  • 3
  • 2
1 Solution
 
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
 
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now