Returning multiple rows from LOOP inside procedure

[the_oliver]

Hi,

 

I have written the following procedure:

 

 

DELIMITER $$

DROP PROCEDURE IF EXISTS countInstructors$$

CREATE PROCEDURE countInstructors()
BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE a INT;
	DECLARE cur1 CURSOR FOR SELECT country_id FROM country;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

	OPEN cur1;

	read_loop: LOOP
		FETCH cur1 INTO a;

		IF done THEN
			LEAVE read_loop;
		END IF;

		SELECT count(*) FROM instructors WHERE country_id = a;

	END LOOP;

	CLOSE cur1;
END$$

DELIMITER ;

 

However i only get 1 row returned.  What i'm trying to do is to get a row returned for each time the query in the loop is run (SELECT count(*)...).  How would i go about doing this?  Or am I barking up the wrong tree?

 

Also i get an error about the last line (DELIMITER telling me to check my syntax.  Could anyone give me a pointer there too please?

 

Many thanks,

 

- Oliver

 

 

 

[mikosiko]

Do you have any reason to do that in a procedure?... why not use directly just one select joining both tables?