MySQL - cursor not working in stored procedure

Question

I see this question has been asked before but no answer provided.

I am using a cursor to parse the data returned by a SELECT statement. However, it does not return the data I expect when compared to running the SELECT statement in a command line.

Broker                  Year    Month   Total
Houlder Insurance           2011    10  1
Houlder Insurance           2011    11  1
Houlder Insurance           2011    12  1
Incepta                 2012    6   4
Incepta                 2012    7   6
Incepta                 2012    8   4
Kay International Plc   2011    10  4
Kay International Plc   2011    11  5
Kay International Plc   2011    12  2
Kay International Plc   2012    1   1
Kay International Plc   2012    2   1
Kay International Plc   2012    4   4
Kay International Plc   2012    5   6
Kay International Plc   2012    6   2
Kay International Plc   2012    7   1
Kay International Plc   2012    8   1
Miles Smith                 2011    12  3
Miles Smith                 2012    2   1
Miles Smith                 2012    4   1
Miles Smith                 2012    5   6
Miles Smith                 2012    6   1
Miles Smith                 2012    7   2
Miles Smith                 2012    8   2

When I run the following stored procedure code, It appears to only to return the records relating to the brokers "Kay International" and "Incepta"! Is there a solution to this issue?

    CREATE DEFINER = 'root'@'localhost'
PROCEDURE src_survey.PopulateReportMonthSurveySubmitted()
BEGIN DECLARE broker    TEXT;
  DECLARE yy        INT;
  DECLARE jan       INT;
  DECLARE feb       INT;
  DECLARE mar       INT;
  DECLARE apr       INT;
  DECLARE may       INT;
  DECLARE jun       INT;
  DECLARE jul       INT;
  DECLARE aug       INT;
  DECLARE sep       INT;
  DECLARE toct      INT;
  DECLARE nov       INT;
  DECLARE tdec      INT;

  DECLARE db_broker TEXT;
  DECLARE db_year   INT;
  DECLARE db_month  INT;
  DECLARE db_total  INT;

  DECLARE eof       INT;
  DECLARE cur1 CURSOR FOR SELECT b.company_name AS 'Broker'
                               , year(submit_date_time) AS 'Year'
                               , month(submit_date_time) AS 'Month'
                               , count(*) AS 'Total'
                          FROM
                            survey_request a
                          JOIN survey d
                          ON d.src_reference = a.src_reference
                          JOIN contacts_companies b
                          ON b.reference = a.broker_company_reference
                          JOIN src_status c
                          ON c.reference = a.src_status_reference
                          WHERE
                            underwriter_company_reference = 73
                          GROUP BY
                            broker
                          , year
                          , month
                          ORDER BY
                            broker
                          , year
                          , month;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = 1;

  SET broker = '';
  SET yy = 0;
  SET jan = 0;
  SET feb = 0;
  SET mar = 0;
  SET apr = 0;
  SET may = 0;
  SET jun = 0;
  SET jul = 0;
  SET aug = 0;
  SET sep = 0;
  SET toct = 0;
  SET nov = 0;
  SET tdec = 0;

  TRUNCATE TABLE reportmonthsurveysubmitted;
  OPEN cur1;
  SET eof = 0;

  WHILE eof = 0
  DO
    FETCH cur1 INTO db_broker, db_year, db_month, db_total;
    IF broker <> db_broker THEN
      SET broker = db_broker;
      SET yy = 0;
    END IF;
    IF yy <> db_year THEN
      IF yy <> 0 THEN
        INSERT INTO reportmonthsurveysubmitted VALUES (0, broker, yy, jan, feb, mar, apr, may, jun, jul, aug, sep, toct, nov, tdec);
      END IF;
      SET yy = db_year;
      SET jan = 0;
      SET feb = 0;
      SET mar = 0;
      SET apr = 0;
      SET may = 0;
      SET jun = 0;
      SET jul = 0;
      SET aug = 0;
      SET sep = 0;
      SET toct = 0;
      SET nov = 0;
      SET tdec = 0;
    END IF;
    CASE db_month
    WHEN 1 THEN
      SET jan = db_total;
    WHEN 2 THEN
      SET feb = db_total;
    WHEN 3 THEN
      SET mar = db_total;
    WHEN 4 THEN
      SET apr = db_total;
    WHEN 5 THEN
      SET may = db_total;
    WHEN 6 THEN
      SET jun = db_total;
    WHEN 7 THEN
      SET jul = db_total;
    WHEN 8 THEN
      SET aug = db_total;
    WHEN 9 THEN
      SET sep = db_total;
    WHEN 10 THEN
      SET toct = db_total;
    WHEN 11 THEN
      SET nov = db_total;
    WHEN 12 THEN
      SET tdec = db_total;
    END CASE;

  END WHILE;
  CLOSE cur1;

  INSERT INTO reportmonthsurveysubmitted VALUES (0, broker, yy, jan, feb, mar, apr, may, jun, jul, aug, sep, toct, nov, tdec);

END

Answer 1

You have problem in your while loop. If you will trace, then you will get to know that insert query is not getting fired for "Houlder insurance" in while loop.

First time yy = 0, after setting broker = db_broker and yy = 0. Then, IF yy <> db_year THEN evaluates TRUE, so it will check for yy <> 0, which it is, so making evaluation to FALSE. Then it sets yy = db_year, thus yy becomes 2011. Second time, broker <> db_broker is FALSE, so it will not go fro further checks, then third record as well goes. Now, when fourth record comes for "Incepta", broker <> db_broker evaluates to TRUE as broker has "Houlder insurance" and db_broker has "Incepta", so broker holds "Incepta" and yy = 0. Next, yy <> db_year evaluates TRUE as yy = 0 and db_year has 2012. Next condition yy <> 0 is FALSE, so insert is not executed this time as well, so it as a whole skips "Houlder insurance" records completely.

Following is the corrected procedure

 CREATE DEFINER = 'root'@'localhost'
PROCEDURE src_survey.PopulateReportMonthSurveySubmitted()
BEGIN DECLARE broker    TEXT;
  DECLARE yy        INT;
  DECLARE jan       INT;
  DECLARE feb       INT;
  DECLARE mar       INT;
  DECLARE apr       INT;
  DECLARE may       INT;
  DECLARE jun       INT;
  DECLARE jul       INT;
  DECLARE aug       INT;
  DECLARE sep       INT;
  DECLARE toct      INT;
  DECLARE nov       INT;
  DECLARE tdec      INT;

  DECLARE db_broker TEXT;
  DECLARE db_year   INT;
  DECLARE db_month  INT;
  DECLARE db_total  INT;

  DECLARE eof       INT;
  DECLARE cur1 CURSOR FOR SELECT b.company_name AS 'Broker'
                               , YEAR(submit_date_time) AS 'Year'
                               , MONTH(submit_date_time) AS 'Month'
                               , COUNT(*) AS 'Total'
                          FROM
                            survey_request a
                          JOIN survey d
                          ON d.src_reference = a.src_reference
                          JOIN contacts_companies b
                          ON b.reference = a.broker_company_reference
                          JOIN src_status c
                          ON c.reference = a.src_status_reference
                          WHERE
                            underwriter_company_reference = 73
                          GROUP BY
                            broker
                          , YEAR
                          , MONTH
                          ORDER BY
                            broker
                          , YEAR
                          , MONTH;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = 1;

  SET broker = '';
  SET yy = 0;
  SET jan = 0;
  SET feb = 0;
  SET mar = 0;
  SET apr = 0;
  SET may = 0;
  SET jun = 0;
  SET jul = 0;
  SET aug = 0;
  SET sep = 0;
  SET toct = 0;
  SET nov = 0;
  SET tdec = 0;

  TRUNCATE TABLE reportmonthsurveysubmitted;
  OPEN cur1;
  SET eof = 0;

  WHILE eof = 0
  DO
    FETCH cur1 INTO db_broker, db_year, db_month, db_total;
    IF broker <> db_broker THEN
    IF yy <> 0 THEN
        INSERT INTO reportmonthsurveysubmitted VALUES (0, broker, yy, jan, feb, mar, apr, may, jun, jul, aug, sep, toct, nov, tdec);
    END IF;
    SET broker = db_broker;
    SET yy = 0;
    END IF;
    IF yy <> db_year THEN
    IF yy <> 0 THEN
        INSERT INTO reportmonthsurveysubmitted VALUES (0, broker, yy, jan, feb, mar, apr, may, jun, jul, aug, sep, toct, nov, tdec);
    END IF;

      SET yy = db_year;
      SET jan = 0;
      SET feb = 0;
      SET mar = 0;
      SET apr = 0;
      SET may = 0;
      SET jun = 0;
      SET jul = 0;
      SET aug = 0;
      SET sep = 0;
      SET toct = 0;
      SET nov = 0;
      SET tdec = 0;
    END IF;

    CASE db_month
    WHEN 1 THEN
      SET jan = db_total;
    WHEN 2 THEN
      SET feb = db_total;
    WHEN 3 THEN
      SET mar = db_total;
    WHEN 4 THEN
      SET apr = db_total;
    WHEN 5 THEN
      SET may = db_total;
    WHEN 6 THEN
      SET jun = db_total;
    WHEN 7 THEN
      SET jul = db_total;
    WHEN 8 THEN
      SET aug = db_total;
    WHEN 9 THEN
      SET sep = db_total;
    WHEN 10 THEN
      SET toct = db_total;
    WHEN 11 THEN
      SET nov = db_total;
    WHEN 12 THEN
      SET tdec = db_total;
    END CASE;

  END WHILE;
  CLOSE cur1;

  INSERT INTO reportmonthsurveysubmitted VALUES (0, broker, yy, jan, feb, mar, apr, may, jun, jul, aug, sep, toct, nov, tdec);

END 

Hope it will help you...

Answer 2

Try this:

CREATE DEFINER = 'root'@'localhost'
PROCEDURE src_survey.PopulateReportMonthSurveySubmitted()
BEGIN DECLARE broker    TEXT;
  DECLARE yy        INT;
  DECLARE jan       INT;
  DECLARE feb       INT;
  DECLARE mar       INT;
  DECLARE apr       INT;
  DECLARE may       INT;
  DECLARE jun       INT;
  DECLARE jul       INT;
  DECLARE aug       INT;
  DECLARE sep       INT;
  DECLARE toct      INT;
  DECLARE nov       INT;
  DECLARE tdec      INT;

  DECLARE db_broker TEXT;
  DECLARE db_year   INT;
  DECLARE db_month  INT;
  DECLARE db_total  INT;

  DECLARE eof       INT DEFAULT 0;
  DECLARE cur1 CURSOR FOR SELECT b.company_name AS 'Broker'
                               , YEAR(submit_date_time) AS 'Year'
                               , MONTH(submit_date_time) AS 'Month'
                               , COUNT(*) AS 'Total'
                          FROM
                            survey_request a
                          JOIN survey d
                          ON d.src_reference = a.src_reference
                          JOIN contacts_companies b
                          ON b.reference = a.broker_company_reference
                          JOIN src_status c
                          ON c.reference = a.src_status_reference
                          WHERE
                            underwriter_company_reference = 73
                          GROUP BY
                            broker
                          , YEAR
                          , MONTH
                          ORDER BY
                            broker
                          , YEAR
                          , MONTH;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = 1;

  SET broker = '';
  SET yy = 0;
  SET jan = 0;
  SET feb = 0;
  SET mar = 0;
  SET apr = 0;
  SET may = 0;
  SET jun = 0;
  SET jul = 0;
  SET aug = 0;
  SET sep = 0;
  SET toct = 0;
  SET nov = 0;
  SET tdec = 0;

  TRUNCATE TABLE reportmonthsurveysubmitted;
  OPEN cur1;
  curLoop: 
    LOOP 
         FETCH cur1 INTO db_broker, db_year, db_month, db_total;
        IF eof = 1 THEN 
            CLOSE cur1;
            LEAVE curLoop;
        END IF;

    IF broker <> db_broker THEN
      SET broker = db_broker;
      SET yy = 0;
    END IF;
    IF yy <> db_year THEN
      IF yy <> 0 THEN
        INSERT INTO reportmonthsurveysubmitted VALUES (0, broker, yy, jan, feb, mar, apr, may, jun, jul, aug, sep, toct, nov, tdec);
      END IF;
      SET yy = db_year;
      SET jan = 0;
      SET feb = 0;
      SET mar = 0;
      SET apr = 0;
      SET may = 0;
      SET jun = 0;
      SET jul = 0;
      SET aug = 0;
      SET sep = 0;
      SET toct = 0;
      SET nov = 0;
      SET tdec = 0;
    END IF;
    CASE db_month
    WHEN 1 THEN
      SET jan = db_total;
    WHEN 2 THEN
      SET feb = db_total;
    WHEN 3 THEN
      SET mar = db_total;
    WHEN 4 THEN
      SET apr = db_total;
    WHEN 5 THEN
      SET may = db_total;
    WHEN 6 THEN
      SET jun = db_total;
    WHEN 7 THEN
      SET jul = db_total;
    WHEN 8 THEN
      SET aug = db_total;
    WHEN 9 THEN
      SET sep = db_total;
    WHEN 10 THEN
      SET toct = db_total;
    WHEN 11 THEN
      SET nov = db_total;
    WHEN 12 THEN
      SET tdec = db_total;
    END CASE;

SET eof = 0;        
    END LOOP;

  INSERT INTO reportmonthsurveysubmitted VALUES (0, broker, yy, jan, feb, mar, apr, may, jun, jul, aug, sep, toct, nov, tdec);

END