MySQL CURSOR not fetching desired rows

Question

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();

spencer7593 · Accepted Answer · 2017-10-18 01:03:29Z

I think the problem is that retString is initialized to NULL.

And we know the expression:

  CONCAT(NULL,'something')

evaluates to NULL. No matter how many non-NULL values we add, it's still gonna be NULL.

Try initializing retString to a non-NULL value before the loop.

  SET retString = 'foo';

And see what you get. I suspect that starting with an empty string will get you what you are after:

  SET retString = '';

This could also be specified as a DEFAULT for the procedure variable, instead of a separate SET statement.

If any of the arguments in the CONCAT function evaluate to NULL, then CONCAT will return NULL. (Consider what happens when product_name or list_price is NULL.) MySQL has a handy function for testing for a NULL value and returning something else...

 IFNULL(foo,'bar')

is shorthand for IF(foo IS NULL,'bar',foo)

Lucas · Accepted Answer · 2017-10-18 01:16:50Z

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 ;

Collectives™ on Stack Overflow

MySQL CURSOR not fetching desired rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related