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$$

CodePudding user response：

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);');