Search code examples
mysqlcursor

MySQL CURSOR not fetching desired rows


The below code is only looping through the cursor once, and in that one loop, it is setting product_name and list_price to null. I have run that SELECT statement (the one for the CURSOR) by itself and it returns 4 results. I am unsure how or why it does not loop through all 4 times and why, on its single loop that it is doing, is not using the first records values for product_name and list_price

DROP PROCEDURE IF EXISTS test;
DELIMITER //
CREATE PROCEDURE test()
BEGIN
    DECLARE retString VARCHAR(1000);
    DECLARE rowNotFound TINYINT DEFAULT FALSE;
    DECLARE product_name VARCHAR(255);
    DECLARE list_price DECIMAL(10,2);

    DECLARE prodCursor CURSOR FOR 
        SELECT product_name, list_price FROM products WHERE list_price > 700 ORDER BY list_price DESC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND 
        SET rowNotFound = TRUE;

    OPEN prodCursor;

    WHILE rowNotFound = FALSE DO
        FETCH prodCursor INTO product_name, list_price;
        SET retString = CONCAT(retString, '"', product_name, '","' , list_price, '"|');
    END WHILE;

    CLOSE prodCursor;

    SELECT retString AS 'Message';
END//
DELIMITER ;

CALL test();

Solution

  • The following ended up being the answer. I had two problems, first the comparisons in the SELECT statement needed to be quantified better and (as @spencer7593 pointed out) retString was initialized to null.

    DROP PROCEDURE IF EXISTS test;
    DELIMITER //
    CREATE PROCEDURE test()
    BEGIN
        DECLARE retString VARCHAR(1000);
        DECLARE rowNotFound TINYINT DEFAULT FALSE;
        DECLARE product_name VARCHAR(255);
        DECLARE list_price DECIMAL(10,2);
    
        DECLARE prodCursor CURSOR FOR 
            SELECT p.product_name, p.list_price FROM products p WHERE p.list_price > 700.00 ORDER BY p.list_price DESC;
        DECLARE CONTINUE HANDLER FOR NOT FOUND 
            SET rowNotFound = TRUE;
    
        SET retString = '';
        OPEN prodCursor;
    
        WHILE rowNotFound = FALSE DO
            FETCH prodCursor INTO product_name, list_price;
            SET retString = CONCAT(retString, '"', product_name, '","' , list_price, '"|');
        END WHILE;
    
        CLOSE prodCursor;
    
        SELECT retString AS 'Message';
    END//
    DELIMITER ;