MySQL While Loop Condition Never being Met somehow?

Issue

I’m trying to create a procedure for the first time to insert different column values from one table to another based on specific conditions. I believe that my while loop isn’t running here:

DELIMITER $$
CREATE PROCEDURE Interpolate(startingRow INT, startingSecond INT, finalYear INT, finalMonth INT, finalDay INT)
BEGIN
DECLARE iterSecond INT DEFAULT 0; #the second we'll use to see if we have prev second below it and curr second after it

DECLARE currMonth INT DEFAULT 0; #these three after help determine when to stop
DECLARE currDay INT DEFAULT 0;
DECLARE currYear INT DEFAULT 0;

DECLARE currSecond INT DEFAULT 0;
DECLARE currBx DOUBLE DEFAULT 0;
DECLARE currBy DOUBLE DEFAULT 0;
DECLARE currBz DOUBLE DEFAULT 0;

DECLARE prevSecond INT DEFAULT 0; #set it to starting second b.c on first time don't want to insert
DECLARE prevBx DOUBLE DEFAULT 0;
DECLARE prevBy DOUBLE DEFAULT 0;
DECLARE prevBz DOUBLE DEFAULT 0;

DECLARE intBx DOUBLE DEFAULT 0;
DECLARE intBy DOUBLE DEFAULT 0;
DECLARE intBz DOUBLE DEFAULT 0;

DECLARE currRowNum INT DEFAULT 0;
SET currRowNum = startingRow;
SET iterSecond = startingSecond;

WHILE (currYear <> finalYear AND currMonth <> finalMonth AND currDay <> finalDay) DO
    SET currSecond = (SELECT seconds FROM apollo12 WHERE row_num = currRowNum);
    SET currYear = (SELECT year FROM apollo12 WHERE row_num = currRowNum);
    SET currMonth = (SELECT month FROM apollo12 WHERE row_num = currRowNum);
    SET currDay = (SELECT day FROM apollo12 WHERE row_num = currRowNum);
    SET currBx = (SELECT b_x FROM apollo12 WHERE row_num = currRowNum);
    SET currBy = (SELECT b_y FROM apollo12 WHERE row_num = currRowNum);
    SET currBz = (SELECT b_z FROM apollo12 WHERE row_num = currRowNum);
    IF prevSecond < iterSecond AND currSecond > iterSecond THEN
        SET intBx = (prevBx + (iterSecond - prevSecond)*(currBx - prevBx)/(currSecond - prevSecond));
        SET intBy = (prevBy + (iterSecond - prevSecond)*(currBy - prevBy)/(currSecond - prevSecond));
        SET intBz = (prevBz + (iterSecond - prevSecond)*(currBz - prevBz)/(currSecond - prevSecond));
        INSERT INTO apollo12int(year, month, day, hour, minute, seconds, b_x, b_y, b_z) SELECT year, month, day, hour, minute, iterSecond, intBx, intBy, intBz FROM apollo12 WHERE row_num = currRowNum;
        SET iterSecond = (iterSecond + 1);
    END IF;
    IF currSecond = iterSecond THEN
        INSERT INTO apollo12int(year, month, day, hour, minute, seconds, b_x, b_y, b_z) SELECT year, month, day, hour, minute, seconds, b_x, b_y, b_z FROM apollo12 WHERE row_num = currRowNum;
        SET iterSecond = (iterSecond + 1);
    END IF;
    SET prevSecond = currSecond;
    SET prevBx = currBx;
    SET prevBy = currBy;
    SET prevBz = currBz;
    SET currRowNum = currRowNum + 1;
END WHILE;
END$$

DELIMITER ;

and this procedure doesn’t work when I write CALL Interpolate(1, 47, 1969, 11, 20). Any thoughts?

Solution

Your while only stops if all three conditions is true

In your case 1969-11-20

So you haven’t that in any of your rows since starting row, you will never get an end.

You could check first if such a row exists or you find another condition to sop the while loop

Answered By – nbk

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0