1

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

Stella
  • 19
  • 6
  • You aren’t setting delimiters – P.Salmon Aug 24 '22 at 18:34
  • It has i just didn't include it – Stella Aug 25 '22 at 00:28
  • My first thought given you did not provide the full error message and you are using heidisql is that you had not set delimiters if that was true then you would get a 1064 error. It's very important that you include the full error message since in this case a 1064 error could occur because you did not set delimiters, the procedure code was syntactically incorrect or the created sql statement is found to be incorrect when submitted Also not including the full error message is a possible deterrent to quick answers – P.Salmon Aug 25 '22 at 07:08
  • Thank you this is helpful .I'll gonna edit the question and put the error message as well. – Stella Aug 25 '22 at 07:59

1 Answers1

0

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);');
P.Salmon
  • 17,104
  • 2
  • 12
  • 19
  • Hi just a follow up question how to execute it using an sql call statement?? If I wanted to get data from past 7 days?? – Stella Aug 25 '22 at 08:39
  • call procedurename(parameter1,parameter2); in the order and datatype defined in the create statement. – P.Salmon Aug 25 '22 at 08:41
  • Hello would you mind giving me a sample syntax for the call? Thanks. – Stella Aug 26 '22 at 02:22