Cursor in stored procedure in MySQL

Question

I have this code:

DELIMITER //

CREATE PROCEDURE GetColumnMaxLengths(IN schema_name 
VARCHAR(255), IN table_name VARCHAR(255))
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE column_name VARCHAR(255);
DECLARE data_type VARCHAR(255);
DECLARE column_type VARCHAR(255);

DECLARE cur CURSOR FOR
    SELECT
        `COLUMN_NAME`,
        `DATA_TYPE`,
        `COLUMN_TYPE`
    FROM
        `INFORMATION_SCHEMA`.`COLUMNS`
    WHERE
        `TABLE_SCHEMA` = schema_name
        AND
        `TABLE_NAME` = table_name
        AND
        `DATA_TYPE` NOT IN (
            "date","time","year","datetime","timestamp",
            "enum","set",
            "geometry","point","linestring","polygon",
            "multipoint","multilinestring","multipolygon","geometrycollection"
         );

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

DROP TEMPORARY TABLE IF EXISTS `_tmp`;
CREATE TEMPORARY TABLE `_tmp` (
    `column_name` VARCHAR(255) NOT NULL,
    `data_type` VARCHAR(255) NOT NULL,
    `column_type` VARCHAR(255) NOT NULL,
    `max_value` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`column_name`)
) ENGINE = InnoDB;

OPEN cur;

read_loop:
LOOP
    FETCH cur INTO column_name, data_type, column_type;
    IF done THEN
        LEAVE read_loop;
    END IF;

    SET @sql_query = CONCAT('SELECT MAX(LENGTH(`', column_name, '`)) INTO @max_value FROM `', schema_name, '`.`', table_name, '`;');
    PREPARE stmt FROM @sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    INSERT INTO `_tmp` (`column_name`, `data_type`, `column_type`, `max_value`) VALUES(column_name, data_type, column_type, @max_value);
END LOOP read_loop;

CLOSE cur;

SELECT `column_name`, `data_type`, `column_type`, `max_value` FROM `_tmp`;

DROP TEMPORARY TABLE IF EXISTS `_tmp`;

END;
//

DELIMITER ;

The execution process proceeds without encountering any errors; however, I am facing a situation where the temporary table, despite the absence of errors, fails to accumulate any data. The dilemma that perplexes me is whether the cursor, responsible for fetching records, is indeed performing this task correctly, or if the root of the problem lies within the intricacies of the insert query aimed at populating the aforementioned temporary table.

Can someone help me?

Answer 1

You should avoid giving parameters and declared variables the same name as table columns. in your case AND TABLE_NAME = table_name is a problem.

I suggest

DELIMITER //

CREATE PROCEDURE p(IN schema_name 
VARCHAR(255), IN ptable_name VARCHAR(255))
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE vcolumn_name VARCHAR(255);
DECLARE vdata_type VARCHAR(255);
DECLARE vcolumn_type VARCHAR(255);

DECLARE cur CURSOR FOR
    SELECT
        `COLUMN_NAME`,
        `DATA_TYPE`,
        `COLUMN_TYPE`
    FROM
        `INFORMATION_SCHEMA`.`COLUMNS`
    WHERE
        `TABLE_SCHEMA` = schema_name
        AND
        `table_name` = ptable_name
        AND
        `DATA_TYPE` NOT IN (
            "date","time","year","datetime","timestamp",
            "enum","set",
            "geometry","point","linestring","polygon",
            "multipoint","multilinestring","multipolygon","geometrycollection"
         );

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

DROP TEMPORARY TABLE IF EXISTS `_tmp`;
CREATE TEMPORARY TABLE `_tmp` (
    `column_name` VARCHAR(255) NOT NULL,
    `data_type` VARCHAR(255) NOT NULL,
    `column_type` VARCHAR(255) NOT NULL,
    `max_value` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`column_name`)
) ENGINE = InnoDB;


#select schema_name, ptable_name;
OPEN cur;

read_loop:
LOOP
    FETCH cur INTO vcolumn_name, vdata_type, vcolumn_type;
    #select vcolumn_name,vdata_type,vcolumn_type;
    IF done THEN
        #select concat (done,' leaving');
        LEAVE read_loop;
    END IF;

    SET @sql_query = CONCAT('SELECT MAX(LENGTH(`', vcolumn_name, '`)) INTO @max_value FROM `', schema_name, '`.`', ptable_name, '`;');
    #select @sql_query;
    PREPARE stmt FROM @sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    #select @max_value;

    INSERT INTO `_tmp` (`column_name`, `data_type`, `column_type`, `max_value`) VALUES(vcolumn_name, vdata_type, vcolumn_type, @max_value);
END LOOP read_loop;

CLOSE cur;

SELECT `column_name`, `data_type`, `column_type`, `max_value` FROM `_tmp`;

DROP TEMPORARY TABLE IF EXISTS `_tmp`;

END;
//

DELIMITER ;

Feel free to add debugging selects