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
Post a Comment