Mysql Stored Procedure: how to handle empty result set

Question

I have written a procedure in which one statement is not executing properly:

SELECT thumb_image into v_thumb_image FROM RESTAURANT_IMAGE WHERE 
   RESTAURANT_ID = v_restaurant_id

The reason, I investigated is if at any point of time resultset is empty, procedure doesn't run statements further.

Please note that I am calling this within a LOOP.

My concern is not to stop execution if for any v_restaurant_id, resultset is empty.

FULL PROCEDURE:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `populate_restaurant_details`()
BEGIN
 DECLARE v_finished_cuisines, 
         v_finished, 
         v_restaurant_id, 
         v_count_discount
 INT DEFAULT 0;

 DECLARE v_cuisines, 
         v_thumb_image 
 varchar(200) DEFAULT "";

 DECLARE cuisine_title varchar(50) DEFAULT "";
 -- Fetch all restaurant id
 DECLARE restaurant_cursor CURSOR FOR
   SELECT id FROM delhifoodonline.restaurant order by id desc;

 DECLARE CONTINUE HANDLER 
  FOR NOT FOUND SET v_finished = 1;

 OPEN restaurant_cursor;

 get_restaurant: LOOP   

   FETCH restaurant_cursor INTO v_restaurant_id;
   IF v_finished = 1 THEN 
    LEAVE get_restaurant;
   END IF;

  SET v_finished_cuisines =""; 
  SET v_thumb_image = "";
begin
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_thumb_image = NULL;

  SELECT thumb_image into v_thumb_image 
  FROM restaurant_image 
  WHERE restaurant_id = v_restaurant_id
  ORDER BY id
  LIMIT 1;
end;

  SELECT count(*) into v_count_discount FROM restaurant_discount WHERE 
    restaurant_id = v_restaurant_id;

BLOCK2: BEGIN

  DECLARE cuisines_cursor CURSOR FOR 
   SELECT cuisine.title FROM restaurant_cuisine INNER JOIN cuisine 
    ON restaurant_cuisine.cuisine_id = cuisine.id
    WHERE 
    restaurant_cuisine.restaurant_id = v_restaurant_id
    LIMIT 0,5;

  DECLARE CONTINUE HANDLER 
    FOR NOT FOUND SET v_finished_cuisines = 1;
  SET v_cuisines = "";
  OPEN cuisines_cursor;

  get_cuisine: LOOP
   FETCH cuisines_cursor INTO cuisine_title;

   IF v_finished_cuisines = 1 THEN 
    LEAVE get_cuisine;
   END IF;

   SET v_cuisines = CONCAT(cuisine_title,", ",v_cuisines);

   END LOOP get_cuisine;
  CLOSE cuisines_cursor;

END BLOCK2;

  SET v_cuisines = TRIM(BOTH ", " FROM v_cuisines);

  IF v_count_discount > 0 THEN
   SET v_count_discount = 1;
  ELSE
   SET v_count_discount = 0;
  END IF;

  UPDATE restaurant SET 
                        thumb_image = v_thumb_image,
                        cuisines_list = v_cuisines,
                        discount_available = v_count_discount
                   WHERE id= v_restaurant_id;
 END LOOP get_restaurant;

CLOSE restaurant_cursor;

END

Answer 1

From the documentation:

NOT FOUND is shorthand for the class of SQLSTATE values that begin with '02'. This is relevant within the context of cursors and is used to control what happens when a cursor reaches the end of a data set. If no more rows are available, a No Data condition occurs with SQLSTATE value '02000'. To detect this condition, you can set up a handler for it (or for a NOT FOUND condition). For an example, see Section 13.6.6, “Cursors”. This condition also occurs for SELECT ... INTO var_list statements that retrieve no rows.

So your select from restaurant_image table also meets the NOT FOUND state when it returns no rows, and invokes the defined handler which causes leaving the loop.

One solution is to declare another handler for that select by putting it inside a BEGIN...END block:

begin
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_thumb_image = NULL;

  SELECT thumb_image into v_thumb_image 
  FROM restaurant_image 
  WHERE restaurant_id = v_restaurant_id
  ORDER BY id
  LIMIT 1;
end;

---

After all, why are doing that using a stored procedure and cursors which would be slow. You can achieve the same functionality executing a single statement:

UPDATE restaurant 
SET thumb_image = (
    SELECT thumb_image 
    FROM restaurant_image 
    WHERE restaurant_id = restaurant.id
    ORDER BY id
    LIMIT 1),
discount_available = IF(EXISTS(
    SELECT 1
    FROM restaurant_discount 
    WHERE restaurant_id = restaurant.id), 1, 0), 
cuisines_list = (
    SELECT group_concat(cuisine.title separator ', ')
    FROM restaurant_cuisine
    INNER JOIN cuisine ON restaurant_cuisine.cuisine_id = cuisine.id
    WHERE restaurant_cuisine.restaurant_id = restaurant.id
    LIMIT 0,5)

Or make it even faster by eliminating sub queries for every row:

UPDATE restaurant r
LEFT JOIN 
    (SELECT restaurant_id, count(*) AS discount_available
    FROM restaurant_discount 
    GROUP BY restaurant_id) d ON r.id = d.restaurant_id
LEFT JOIN 
    (SELECT restaurant_id, thumb_image 
    FROM restaurant_image r1
    WHERE NOT EXISTS (
        SELECT 1 FROM restaurant_image r2 WHERE r2.restaurant_id = r1.restaurant_id AND r2.id < r1.id
    )) t ON r.id = t.restaurant_id
LEFT JOIN
    (SELECT rc.restaurant_id, SUBSTRING_INDEX(GROUP_CONCAT(c.title SEPARATOR ', '), ',', 5) AS cuisines_list
    FROM restaurant_cuisine rc
    INNER JOIN cuisine c ON rc.cuisine_id = c.id
    GROUP BY rc.restaurant_id
    ) rc ON r.id = rc.restaurant_id
SET r.discount_available = IF(d.discount_available = 0, 0, 1),
r.thumb_image = t.thumb_image,
r.cuisines_list = rc.cuisines_list

Try these sub-queries separately to find a better understanding.