How to both reference and return a result set from a stored procedure in mysql?

Question

I have read that a select query that stores a result into variables does not return a result set. Is there an efficient way to do both?
BEGIN
   #return this and also use it in the following query
   select id, name, table2_id 
   from table1 
   where id = variable_id;

select id, table2_name 
   from table2 
   where table2.id = table2.id from the previous result set
END

nbk · Answer

Here you can use user defined variables, as long as you only have only one table2.id
BEGIN
   #return this and also use it in the following query
   select id, name, @id :=  table2_id 
   from table1 
   where id = variable_id;

select id, table2_name 
   from table2 
   where table2.id = @id
END

Or if you have more than one table2.id
BEGIN
   #return this and also use it in the following query
   select id, name, table2.id 
   from table1 
   where id = variable_id;

select id, table2_name 
   from table2 
   where table2.id in (select table2<_id 
                       from table1 
                       where id = variable_id);
END

Or you can join directly, and ave only one result set
BEGIN
   #return this and also use it in the following query
   select t1.id, t1.name, t1.table2_id,t2,id, t2.table2_name
   from table1 t1 INNER JOIN table2 t2 ON t2.id = t1.table2_id
   where t1.id = variable_id;
END

How to both reference and return a result set from a stored procedure in mysql?

One Answer

Add your own answers!

Ask a Question