Mysql: Make a list of values from procedure result

Question

I have a stored procedure that performs a SELECT of ids (the reason it is inside a procedure is that it at first checks which ids to select).

I would like to use that result set in a procedure query like SELECT * FROM products WHERE productID IN <resultSet>.

But I do not know how to get the result set of the procedure into a range/interval/...? variable to perform that selection. What am I missing?

Edit: This question is not really a duplicate of SQL server stored procedure return a table, it just has the same solution: Stored Procedures do not return anything.

A procedure in mysql cannot directly return a resultset (to be further used inside mysql; it can return it to a client though). You can insert that values into a (temporary) table and then use `SELECT * FROM products WHERE productID IN (select id from thattable)`, or try to apply the logic of your procedure/idselection directly into that query, e.g. `SELECT * FROM products WHERE productID IN (select id from maintable where is_a_very_nice_product = 1)` — Solarflare, Aug 23 '17 at 10:28
@Solarflare: Unfortunately, the procedure does something like `IF xyz THEN query1; ELSE query2; END IF`, which is the reason for it being a stored procedure at all. — Bowi, Aug 23 '17 at 11:44
Well, that's why I said "try", I am aware that it is not always possible. If it's not possible to merge your code or redesign your data model, you'll have to use a table or read the ids in e.g. a php client and put them in the querycode. Apart from that: you may not have tried hard enough: although it might not be worth to do it, you could maybe use `...WHERE productID IN (select id from query 1 and xyz union select id from query2 and not xyz)`. Or e.g. run `IF xyz THEN select * FROM products WHERE productID IN (query1); ELSE select * FROM ... IN (query2); END IF` directly in your procedure. — Solarflare, Aug 23 '17 at 12:05

score 3 · Accepted Answer · answered Aug 23 '17 at 16:10

MySQL stored procedures doesn't return values, BUT you can store a result in a temporary table and use them in another query, how?

In your stored procedure, create a temporary table with result values:

create procedure your_procedure()

    drop temporary table if exists _procedure_result_tmp;

    -- dump the result in a temporary table

    create temporary table _procedure_result_tmp 
      select id from your_table where <condition>;

 end

Use the _procedure_result_tmp table in your query:

call your_procedure();
select * FROM your_other_table 
  where id in (select id from _procedure_result_tmp);

Mysql: Make a list of values from procedure result

1 Answers1