How to both reference and return a result set from a stored procedure in mysql?
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&lt;_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