I have written a MYSQL procedure and there is mistake in syntax, can't figure out the correct syntax

Question

The MYSQL procedure will
1. Create TABLE1_ARCHIVE similar to TABLE1 if it is not there.
2.Insert the values from the TABLE1 into TABLE1_ARCHIVE based on date condition
3. Delete those records from TABLE1.

Link to the code: https://paiza.io/projects/Eq7I5YGo-lt7_gu8wpQNdg?language=mysql

CREATE PROCEDURE ARCHIVE_EVENTS ( IN f_table VARCHAR(255),
                                IN t_table VARCHAR(255),
                                IN t_ts TIMESTAMP)
BEGIN
    DECLARE c_sql VARCHAR(255);
     DECLARE i_sql VARCHAR(255);
     DECLARE d_sql VARCHAR(255);    

SET @c_sql = CONCAT(' CREATE TABLE IF NOT EXISTS ', @t_table , ' LIKE ', @f_table );
PREPARE stmt FROM  @c_sql;
EXECUTE stmt ;

SET @i_sql = CONCAT(' INSERT INTO ', @t_table, ' SELECT * FROM ', @f_table, ' WHERE `event_date` <=  ', @t_ts);
PREPARE stmt FROM  @i_sql;
EXECUTE stmt ;

COMMIT;

SET @d_sql = CONCAT(' DELETE FROM ', @f_table, ' WHERE `event_date` <= ', @t_ts);
PREPARE stmt FROM  @d_sql;
EXECUTE stmt ;
COMMIT;
END;

CALL ARCHIVE_EVENTS ('TABLE1', 'TABLE1_ARCHIVE', now());

---

After doing the changes, the procedure looks like this and is still not working :

The error that i am getting is

ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

CREATE PROCEDURE ARCHIVE_EVENTS (IN f_table VARCHAR(255),IN t_table VARCHAR(255),IN t_ts TIMESTAMP)
BEGIN
    DECLARE c_sql VARCHAR(255);
    DECLARE i_sql VARCHAR(255);
    DECLARE d_sql VARCHAR(255);    

    SET c_sql = CONCAT(' CREATE TABLE IF NOT EXISTS ', t_table , ' LIKE ', f_table);
    PREPARE stmt FROM  c_sql;
    EXECUTE stmt ;

    SET i_sql = CONCAT(' INSERT INTO ', t_table, ' SELECT * FROM ', f_table, ' WHERE `event_date` <= ', t_ts);
    PREPARE stmt FROM  i_sql;
    EXECUTE stmt ;

    COMMIT;

    SET d_sql = CONCAT(' DELETE FROM ', f_table, ' WHERE `event_date` <= ', t_ts);
    PREPARE stmt FROM  d_sql;
    EXECUTE stmt ;
    COMMIT;
END;

CALL ARCHIVE_EVENTS ('TEST', 'TEST_ARCHIVE', now());

Answer 1

It isn't difficult to debug a procedure like this just put in a few select statements. Note @variables have to be used in the prepared statements and you have not quoted the date t_ts. And possibly you have not set delimiters.

    drop procedure if exists p;
    delimiter $$

    CREATE PROCEDURE p (IN f_table VARCHAR(255),IN t_table VARCHAR(255),IN t_ts TIMESTAMP)
    BEGIN

    SET @c_sql = (select CONCAT(' CREATE TABLE IF NOT EXISTS ', t_table , ' LIKE ', f_table));
    select @c_sql;

    /*PREPARE stmt FROM  @c_sql;
        EXECUTE stmt ;
         deallocate prepare stmt;
    */   
    SET @i_sql = (select CONCAT(' INSERT INTO ', t_table, ' SELECT * FROM ', f_table, ' WHERE `event_date` <= ',char(39), t_ts,char(39)));
    select @i_sql;
    /*
    PREPARE stmt FROM  @i_sql;
    EXECUTE stmt ;
    deallocate prepare stmt;

    COMMIT;
    */
    SET @d_sql = (select CONCAT(' DELETE FROM ', f_table, ' WHERE `event_date` <= ', char(39),t_ts, char(39)));
    select @d_sql;
    /*
    PREPARE stmt FROM  @d_sql;
    EXECUTE stmt ;
    deallocate prepare stmt;
    COMMIT;
    */
END $$
delimiter ;
drop table users_copy;
CALL p('users', 'users_copy', now());

Yield these statements
+---------------------------------------------------+
| @c_sql                                            |
+---------------------------------------------------+
|  CREATE TABLE IF NOT EXISTS users_copy LIKE users |
+---------------------------------------------------+
1 row in set (0.00 sec)

+-----------------------------------------------------------------------------------------+
| @i_sql                                                                                  |
+-----------------------------------------------------------------------------------------+
|  INSERT INTO users_copy SELECT * FROM users WHERE `event_date` <= '2018-05-09 19:36:54' |
+-----------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

+----------------------------------------------------------------+
| @d_sql                                                         |
+----------------------------------------------------------------+
|  DELETE FROM users WHERE `event_date` <= '2018-05-09 19:36:54' |
+----------------------------------------------------------------+
1 row in set (0.04 sec)

You could then test each of them if you wished or uncomment the prepared statements and let rip.

Answer 2

DROP PROCEDURE IF EXISTS ARCHIVE_EVENTS;
CREATE PROCEDURE ARCHIVE_EVENTS (IN f_table CHAR(100),IN t_table 
CHAR(100),IN t_ts TIMESTAMP)
READS SQL DATA
COMMENT 'Test'
BEGIN

SET @c_sql = CONCAT(' CREATE TABLE IF NOT EXISTS ', t_table , ' LIKE ', f_table);
PREPARE stmt FROM  @c_sql;
EXECUTE stmt ;

SET @i_sql = CONCAT(' INSERT INTO ', t_table, ' SELECT * FROM ', f_table, ' WHERE `dateTime` <= ', DATE(t_ts));
PREPARE stmt1 FROM  @i_sql;
EXECUTE stmt1 ;

COMMIT;

SET @d_sql = CONCAT(' DELETE FROM ', f_table, ' WHERE `dateTime` <= ', DATE(t_ts));
PREPARE stmt2 FROM  @d_sql;
EXECUTE stmt2 ;
COMMIT;
END;

CALL ARCHIVE_EVENTS ('TEST', 'WS_REL_TEST', now());

Answer 3

CREATE PROCEDURE ARCHIVE_EVENTS (IN f_table CHAR(100),IN t_table 
CHAR(100),IN t_ts TIMESTAMP)

BEGIN

SET @c_sql = CONCAT(' CREATE TABLE IF NOT EXISTS ', t_table , ' LIKE ', f_table);

SET @i_sql = CONCAT(' INSERT INTO ', t_table, ' SELECT * FROM ', f_table, ' WHERE `event_date` <= ',char(39), t_ts,char(39));

SET @d_sql = CONCAT(' DELETE FROM ', f_table, ' WHERE `event_date` <= ',char(39), t_ts,char(39));

PREPARE stmt FROM  @c_sql;
PREPARE stmt1 FROM  @i_sql;
PREPARE stmt2 FROM  @d_sql;


EXECUTE stmt ;
EXECUTE stmt1 ;
EXECUTE stmt2 ;
COMMIT;

END;

This is the working code, thanks to the above answers. This is the final solution for the query that worked for me.