MySQL Stored procedure with cursor to find all error date values

Question

I'm trying to find all entries in my Database where value for the column 'created' is not a timestamp.

I wrote a stored procedure, using a cursor for getting all tables having a 'created' column.

    DROP PROCEDURE IF EXISTS `findAllErrorDate`;
delimiter //
CREATE PROCEDURE `findAllErrorDate`()
BEGIN
  DECLARE tableNames varchar(100);  
  DECLARE done INTEGER DEFAULT 0;
  DECLARE nameCursor CURSOR FOR SELECT distinct `TABLE_NAME` 
        FROM  `INFORMATION_SCHEMA`.`columns`
        WHERE `COLUMN_NAME` IN ('ID', 'created', 'modified')
        AND `TABLE_SCHEMA` = 'myDB';    
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN nameCursor;
    read_loop: LOOP
        FETCH nameCursor INTO tableNames;
        IF done THEN
        LEAVE read_loop;
        END IF;

        SELECT ID, created, modified 
        FROM tableNames 
        WHERE created not like '20%' 
        AND modified not like '20%';

    END LOOP;
  CLOSE nameCursor;  
END//
delimiter ;

CALL `findAllErrorDate`();

But I get an error :

Error Code: 1146. Table 'myDB.tablenames' doesn't exist

Can someone tell me where I made a mistake?

Thanks in advance!

Answer 1

The tableNames is a variable you can't use it as table name, but you can use a prepared statement from concatenated string:

SET @sql = CONCAT('SELECT ID, created, modified  FROM ',tableName,
        'WHERE created not like ''20%'' AND modified not like ''20%''');
PREPARE stmt_select FROM @sql;
EXECUTE stmt_select;


DEALLOCATE PREPARE stmt_select;