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