title: MYSQL - Combine multiple result sets in stored procedure
body: <p>I have the following stored procedure:</p>
  
  <pre><code>DELIMITER //
  
  DROP PROCEDURE IF EXISTS active_locations;
  
  CREATE PROCEDURE active_locations()
  
  BEGIN
  
  DECLARE y INT DEFAULT 1;
  DECLARE LOC VARCHAR(200);
  
  WHILE y &lt; 6 DO
  
  SELECT CONCAT('SELECT table_sample.LOC', GROUP_CONCAT(y), '_Status'
                          ' FROM table_sample') INTO @LOC;
  
  SET y = y + 1;
  
  PREPARE stmt FROM @LOC;
  EXECUTE stmt;
  
  END WHILE;
  
  END//
  </code></pre>
  
  <p>However, when I call this procedure in Navicat, I'm getting multiple result sets for each iteration of y (so in this case, 5). I've tried inserting the results into a temporary table but can only get the results where y = 1. I'm not familiar with stored procedures but I think that for each loop of y, MySQL is executing the 
  <code>SELECT table_sample.LOC', GROUP_CONCAT(y), '_Status' ' FROM table_sample</code> in a separate window.</p>
  
  <p>Is there anyway to combine these columns into one table but while still displaying the data column by column?</p>