mysql stored procedure to deleted data from table

Question

i am trying to write a stored procedure in MySQL as cleaner which will be used to deleted data from tables (one stored procedure for all tables)

    CREATE PROCEDURE `cleaner`(
     IN table_name_in varchar(255),
     IN field_name_in varchar(255),
     IN day_in int
    )
    BEGIN
    DECLARE FoundCount INT;
    SELECT COUNT(1) INTO FoundCount
    FROM table_name_in where STR_TO_DATE(field_name_in, "%Y-%m-%d") < STR_TO_DATE(NOW() - INTERVAL day_in DAY, "%Y-%m-%d");
    IF FoundCount = 1 THEN
       SET SQL_SAFE_UPDATES = 0;
       DELETE from table_name_in where STR_TO_DATE(field_name_in, "%Y-%m-%d") < STR_TO_DATE(NOW()- INTERVAL day_in DAY, "%Y-%m-%d");
        SET SQL_SAFE_UPDATES = 1;
        select  "true" as isDeleted , "true" as isValuePresent ;
    else
        select  "false" as isDeleted , "false" as isValuePresent ;
   END IF;
   END

and i call it as

   call cleaner('employee','created_on',30)

which means deleted all record from employee table which are 30 days older using field created_on

but it gives me an error message saying

 Error Code: 1146. Table 'table_name_in' doesn't exist

where as employee table exists but it is not taking employee as a parameter

Check this thread it might help you http://stackoverflow.com/questions/6609778/mysql-store-procedure-dont-take-table-name-as-parameter — Abhik Chakraborty, Sep 23 '14 at 10:07

score 1 · Accepted Answer · edited May 23 '17 at 12:29

This worked for me

          CREATE PROCEDURE `cleaner`(
           IN table_name_in varchar(255),
           IN field_name_in varchar(255),
           IN day_in int
          )
          BEGIN
            SET SQL_SAFE_UPDATES = 0;
            SET @sql = CONCAT('DELETE FROM ',table_name_in,' WHERE STR_TO_DATE(',field_name_in,', "%Y-%m-%d") < STR_TO_DATE(NOW() - INTERVAL ',day_in,' DAY, "%Y-%m-%d")');
            PREPARE s1 from @sql;
            EXECUTE s1;
            SET SQL_SAFE_UPDATES = 1;
         END

Sources: Mysql stored procedure don't take table name as parameter

mysql stored procedure to deleted data from table

1 Answers1