mysql stored procedure to deleted data from table -


i trying write stored procedure in mysql cleaner used deleted data tables (one stored procedure 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) foundcount     table_name_in str_to_date(field_name_in, "%y-%m-%d") < str_to_date(now() - interval day_in day, "%y-%m-%d");     if foundcount = 1        set sql_safe_updates = 0;        delete table_name_in 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" isdeleted , "true" isvaluepresent ;     else         select  "false" isdeleted , "false" isvaluepresent ;    end if;    end 

and call as

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

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

but gives me error message saying

 error code: 1146. table 'table_name_in' doesn't exist 

where employee table exists not taking employee parameter

this worked 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 ',table_name_in,' str_to_date(',field_name_in,', "%y-%m-%d") < str_to_date(now() - interval ',day_in,' day, "%y-%m-%d")');             prepare s1 @sql;             execute s1;             set sql_safe_updates = 1;          end 

sources: mysql stored procedure don't take table name parameter


Comments

Popular posts from this blog

php - Submit Form Data without Reloading page -

linux - Rails running on virtual machine in Windows -

php - $params->set Array between square bracket -