CODEWITHSUNDEEP
mysqlcursormysql-5.6
Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5767

Return data in the single set from stored procedure using CURSOR/LOOP

I want to return data in the single set, however I'm getting 10 different outputs with single row. I want to have 10 rows in a single set:

DROP TABLE IF EXISTS calendar;
DROP PROCEDURE IF EXISTS p_generate_snapshot;

CREATE TABLE calendar(date date);

INSERT INTO calendar(date) VALUES
('2020-11-01'),
('2020-11-02'),
('2020-11-03'),
('2020-11-04'),
('2020-11-05'),
('2020-11-06'),
('2020-11-07'),
('2020-11-08'),
('2020-11-09'),
('2020-11-10');

DELIMITER $$
CREATE PROCEDURE p_generate_snapshot(start_date date, end_date date)
BEGIN
  
   DECLARE d date;
   DECLARE done INT DEFAULT FALSE;

   DECLARE cursor_name CURSOR FOR SELECT * FROM calendar c WHERE date >= start_date AND date < end_date;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   OPEN cursor_name;
     fetch_loop: LOOP
       FETCH cursor_name INTO d;

       IF done THEN  
         LEAVE fetch_loop;
       END IF;

       SELECT d;
     
     END LOOP;
   CLOSE cursor_name;
END$$

DELIMITER ;

CALL p_generate_snapshot('20201101', '20201201');

Upvotes: 1

Views: 1159

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562661

It's not clear why you are using a cursor for this. Your procedure could be written as:

CREATE PROCEDURE p_generate_snapshot(start_date date, end_date date)
BEGIN  
   SELECT * FROM calendar c WHERE date >= start_date AND date < end_date;
END

That would return one result set as you said you wanted.

But it's possible that your example is simplified and you have other steps you need to do with the rows fetched by your cursor. That would be a legitimate reason to use a cursor.

But I don't think it's possible to return the rows handled by a cursor as if it's one result set. Certainly it is not possible to do it by SELECT d; in each iteration of your cursor loop. That is bound to return a separate result set per row, as you found out.

One workaround, though it is pretty awkward, is to insert the rows to a temporary table during the cursor loop. Then select from the temp table as a last step.

CREATE PROCEDURE p_generate_snapshot(start_date date, end_date date)
BEGIN
  
   DECLARE _d date;
   DECLARE done INT DEFAULT FALSE;

   DECLARE cursor_name CURSOR FOR SELECT date FROM calendar c WHERE date >= start_date AND date < end_date;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

   CREATE TEMPORARY TABLE t (d DATE);

   OPEN cursor_name;
     fetch_loop: LOOP
       FETCH cursor_name INTO _d;

       IF done THEN  
         LEAVE fetch_loop;
       END IF;

       INSERT INTO t SET d = _d;
     
     END LOOP;
   CLOSE cursor_name;

   SELECT d FROM t;
   DROP TABLE t;
END

For that matter, I don't know why you would use a procedure at all, instead of just running that SELECT statement in your client application. That would solve both problems -- handling rows one by one, but still treating it as a single query result.

I almost never use stored procedures in MySQL. I find they are really more trouble than they're worth. I posted reasons why here: https://www.quora.com/What-are-the-reasons-not-to-use-or-not-use-stored-procedures/answer/Bill-Karwin

Upvotes: 1

Related Questions