Hi rephrasing my question earlier I added the full statement below.codes should retain data from past 7 days.Thank you.
CREATE PROCEDURE logs_user_aht_delete(
IN logs_database VARCHAR(25) CHARACTER SET utf,
IN logs_data_retention_db INT(10) )
BEGIN
SET @v = concat('DELETE FROM ',logs_database,'.user_aht ua WHERE
IFNULL(ua.completed_at, ua.inserted_at) < (CURRENT_DATE - 7)- INTERVAL ',logs_data_retention_db ,' DAY);');
PREPARE stm FROM @v;
EXECUTE stm;
DEALLOCATE PREPARE stm;
END$$
To debug this select @v in the procedure commenting out the prepare,exec,deallocate and examine the result.
Two thing are syntactically incorrect 1) the table alias where you are part way to a multi-table delete 2) you are providing the days to be deleted as a parameter and also hard coding it in the build of @v
I suggest you change to
SET @v = concat('DELETE ua FROM ',logs_database,'.user_aht ua
WHERE IFNULL(ua.completed_at, ua.inserted_at) < (CURRENT_DATE - INTERVAL ',logs_data_retention_db ,' DAY);');