robofix
asked on
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.
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.
ASKER
hi rajvja,
what about my sales_report procedure that I need applied for every prodid?
what about my sales_report procedure that I need applied for every prodid?
select * from products prod
inner join proddetails det on prod.prodid=det.prodid
where prod.salesprice < 100
and prod.prodid = @prodid
inner join proddetails det on prod.prodid=det.prodid
where prod.salesprice < 100
and prod.prodid = @prodid
ASKER
i don't think this is correct.
Further suggestions anyone?
Further suggestions anyone?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
inner join proddetails det on prod.prodid=det.prodid
where prod.salesprice < 100