Mein Hat Mein Hat - 5 months ago 10
SQL Question

Cursor Fetch Returns Null

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.

DELIMITER //
CREATE PROCEDURE sp_name
(
IN dynamic_table 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;

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

IF done THEN
LEAVE read_loop;
END IF;

select @my_key_id;

FETCH cr_cursor INTO my_key_id;
END LOOP;

close cr_cursor;

end;

END //
DELIMITER ;

Answer

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.

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

            IF done THEN
                LEAVE read_loop;
            END IF;

            -- select @my_key_id; -- Drew removed -------------------

            FETCH cr_cursor INTO my_key_id;
            select my_key_id; -- Drew added -------------------
        END LOOP;

        close cr_cursor;

    end;

END $$
DELIMITER ;