CODEWITHSUNDEEP
mysqlstored-procedures
Bowi
Bowi

Reputation: 1528

Mysql: Make a list of values from procedure result

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.

Upvotes: 1

Views: 4345

Answers (1)

Ivan Cachicatari
Ivan Cachicatari

Reputation: 4284

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

  1. 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

  2. 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);

Upvotes: 3

Related Questions