Hello everybody,
I'm a rookie, so be nice with me ;)
And to be worst, i'm french so excuse my english ...
I want to create a procedure to see the reliability of the data to be imported including obtaining the percentage of each blank field.
DELIMITER |
CREATE PROCEDURE `proc_champ_NULL` (IN `vi_nomtable` VARCHAR(30) charset utf8)
BEGIN
DECLARE `v_finished` INT DEFAULT 0;
DECLARE `c_nomcol` VARCHAR(40);
DECLARE `v_nbligne` INT DEFAULT 0;
DECLARE `v_result` INT DEFAULT 0;
DECLARE `v_dt_chargement` DATE;
DECLARE done INT DEFAULT 0;
DECLARE `cur_nom_table` CURSOR FOR
SELECT `column_name` FROM information_schema.columns WHERE table_name = `vi_nomtable`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
SELECT '1';
SELECT CURRENT_TIMESTAMP INTO `v_dt_chargement`;
SELECT '2';
CREATE TABLE IF NOT EXISTS `stat_champ_NULL`
(
`nom_table` VARCHAR(30) NULL,
`nom_col` VARCHAR(40) NULL,
`taux_NULL` VARCHAR(4) NULL,
`nb_ligne_total` VARCHAR(4) NULL,
`dt_chargement` DATE NULL
)
ENGINE = InnoDB;
SELECT '3';
OPEN `cur_nom_table`;
SELECT '4';
SELECT COUNT(*) INTO `v_nbligne` FROM `vi_nomtable`;
SELECT '5';
REPEAT
FETCH `cur_nom_table` INTO `c_nomcol`;
SELECT '6';
SELECT SUM(CASE WHEN `c_nomcol`='' THEN 1 ELSE 0 END)*100/`v_nbligne`
INTO `v_result`
FROM `vi_nomtable`;
SELECT '7';
INSERT INTO `stat_champ_NULL`
VALUES (
`vi_nomtable`,
`c_nomcol`,
`v_result`,
`v_nbligne`,
`v_dt_chargement`
)
;
SELECT '8';
UNTIL done END REPEAT;
CLOSE `cur_nom_table`;
SELECT '9';
END|
DELIMITER ;
And when i run it :
CALL proc_champ_null('import_ldapuser');
i get :
Quote
Error Code: 1146 Table 'dbappstore.vi_nomtable' doesn't exist
i put a lot of "select +num" to help me to find where is the problem.
(I have not found another way to help me to debug)
the problem seems to be in line 36. :
SELECT COUNT(*) INTO `v_nbligne` FROM `vi_nomtable`;
Apparently it takes to the name of the variable "vi_nomtable" and not it's value.
Thanks to everyone to try to find a solution fix my problem !
David