Why is the cursor exiting after one iteration?

Question

I have a table that lists tables that I want to get counts off of:

|dq_tbl_id|dq_tbl_tbl_name           |dq_tbl_use_batch_stamp|
|---------|--------------------------|----------------------|
|1        |dev_credly_badges         |1                     |
|...      |...                       |...                   |
|18       |tbl18                     |[NULL]                |

I can't figure out why the procedure exits after one iteration:

CREATE DEFINER=`channel`@`%` PROCEDURE `dq_job_dev_count`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE proc_dq_tbl_id SMALLINT;
DECLARE tbl_count INT DEFAULT 0;
DECLARE tbl_name VARCHAR(35);
DECLARE dq_tbl_use_batch_stamp BOOL DEFAULT FALSE;

DECLARE dq_tbl_ids 
    CURSOR FOR 
        SELECT dq_tbl_id FROM ref_dq_tbl_count;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN dq_tbl_ids;   

WHILE done = 0 DO
    FETCH NEXT FROM dq_tbl_ids INTO proc_dq_tbl_id;

    IF done = 0 THEN
        SELECT dq_tbl_tbl_name, dq_tbl_use_batch_stamp INTO @tbl_name, @dq_tbl_use_batch_stamp FROM ref_dq_tbl_count WHERE dq_tbl_id = @proc_dq_tbl_id;

        IF @dq_tbl_use_batch_stamp = 1 THEN
            SET @sql_stmt = CONCAT('SELECT COUNT(*) INTO @tbl_count FROM ', @tbl_name, ' WHERE DATE(batch_stamp) = CURDATE()');
        ELSE
            SET @sql_stmt = CONCAT('SELECT COUNT(*) INTO @tbl_count FROM ', @tbl_name);
        END IF;

        PREPARE stmt FROM @sql_stmt;
        EXECUTE stmt;

        INSERT INTO prod_dq_tbl_load_counts (tbl_name, row_count) VALUES (@tbl_name, @tbl_count);
    END IF;
END WHILE;

CLOSE dq_tbl_ids;

END

Bill_Karwin_20860 · Answer

You are fetching the cursor result into a local variable:

FETCH NEXT FROM dq_tbl_ids INTO proc_dq_tbl_id;

Subsequently you use a user-defined variable @proc_dq_tbl_id under the mistaken assumption that it contains the value you fetched.

In MySQL stored procedures, a variable with the @ sigil is a user-defined variable. It is literally a different variable than the local variable you created with DECLARE.

You can fetch the cursor into a user-defined variable (with the @ sigil) or you can fetch the cursor into a local variable. But then you must use the same variable in subsequent expressions.

Since @proc_dq_tbl_id is likely to be NULL, the SELECT statement you use it in will never match any rows. Your cursor is probably looping plenty, but the SELECT statement in each loop iteration is finding no matches.

This treatment of variables in MySQL is different than other brands of SQL database (e.g. Microsoft SQL Server). It is sometimes confusing to developers who are accustomed to using other brands.

Why is the cursor exiting after one iteration?

1 Answers