MySQL Forums
Forum List  »  Stored Procedures

Error on variable when running a procedure
Posted by: bob dylan
Date: November 19, 2013 10:31AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Error on variable when running a procedure
2214
November 19, 2013 10:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.