Cursor Fetch Returns Null

Question

The problem is quite clear but I have tried many things to fix it, including using different variable names than table fields. The fetched value from cursor always returns null. The value that is assigned to the cursor fetch is the same data type (int(11)). What I am doing is to assign fetched key_id value from cursor's select table into @my_key_id int(11) variable but it keeps coming as null.

        declare my_key_id int(11); /*variable that will be assigned from the     value in cursor*/
        DECLARE done INT DEFAULT FALSE; /*for cursor break*/

        DECLARE cr_cursor cursor for select key_id from tmp_valuesss;     /*cursor declaration*/
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; /*break     thingy*/

        open cr_cursor;

        read_loop: LOOP

            IF done THEN
                LEAVE read_loop;
            END IF;

            select @my_key_id;

            FETCH cr_cursor INTO my_key_id;
        END LOOP;

        close cr_cursor;

Answer 1

You are mistaking User Variables (the ones with an @ sign) with Local Variables (the ones with DECLARE).

Your User Variable was never set and thus always null.

Also, DEALLOCATE any PREPARE var.

The Fetch was moved up to occur at the beginning of LOOP.

drop procedure if exists calculate_thingy;
delimiter $$
CREATE PROCEDURE calculate_thingy
(
    IN table_name VARCHAR(100)
)
BEGIN
    DECLARE SQL_STATEMENT NVARCHAR(8000);

    drop table if exists tmp_valuesss;

    SET @SQL_STATEMENT = CONCAT('CREATE TABLE IF NOT EXISTS tmp_valuesss AS     (SELECT * FROM ', table_name, ')');    
    PREPARE STMT FROM @SQL_STATEMENT;
    EXECUTE STMT;
    DEALLOCATE PREPARE STMT; -- Drew added -------------------

    alter table tmp_valuesss add the_field float;

    begin
        declare my_key_id int(11); /*variable that will be assigned from the     value in cursor*/
        DECLARE done INT DEFAULT FALSE; /*for cursor break*/

        DECLARE cr_cursor cursor for select key_id from tmp_valuesss;     /*cursor declaration*/
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; /*break     thingy*/

        open cr_cursor;

        read_loop: LOOP
            FETCH cr_cursor INTO my_key_id;
            IF done THEN
                LEAVE read_loop;
            END IF;
            select my_key_id;
        END LOOP;
        close cr_cursor;
    end;
END $$
DELIMITER ;

A few things to note. First, the reason why the whole block is presented above is to aid the future visitor here that may be having a problem with creating the stored proc due to the DELIMITER issue many struggle with. Also, the drop at the beginning is important for edits to the proc the 2nd time and thereafter.

Also, this is clearly just a testing a concept stored proc that you presented. Meaning, by doing a select my_key_id; in the LOOP, you are effectively creating an additional resultset on the consumer side (that which called the stored proc with the call statement). Whether or not you are equipped to handle multiple result sets coming back in your code will impact your review of this.

Also, it is driven by the state of the table name you pass as a parameter. So if that table contains values or nulls, you get what you get based on your code and your decision to pass that table name. A table that allegedly contains the column key_id because that is the way you wrote the stored procedure to begin with.

A view below is of it working, with multiple result sets.

That above image shows a table that had 2 rows in it for fish and frog. Two resultsets were returned (that is how you coded it). I Highlighted the second result set, and it showed the value coming back from it (which was id 2) ... the last row that occurred before the LOOP was finished.

Instead of attempting to edit this answer for a third time, I recommend you join me in chat in the link I gave in a comment under your question.