How to create an Archive Table with a Mysql Procedure and Date()

Question

I try to create a procedure which runs once a day and stores a subset of data from a bigger Table. The Name of this table should be dynamically created with CURDATE().

    DROP PROCEDURE daily_backup;
    DELIMITER |
    CREATE PROCEDURE daily_backup()
    BEGIN
      SET @tbl = CONCAT('items_data_', DATE_FORMAT(CURDATE(), '%Y%m%d'));
      SET @s = CONCAT('DROP TABLE IF EXISTS ', @tbl);
      PREPARE stmt FROM @s;
      EXECUTE stmt;

      @s = CONCAT('CREATE TABLE `', @tb1, '` (`id` int(11) NOT NULL,`up` mediumint(9) NOT NULL,`down` mediumint(9) NOT NULL) ENGINE=Archive');

      PREPARE stmt FROM @s;
      EXECUTE stmt;

      SET @s = CONCAT('INSERT INTO ', @tbl, ' SELECT id,up,down FROM items;');
      PREPARE stmt FROM @s;
      EXECUTE stmt;
    END

These are the results

#1064 - 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 '@s = CONCAT('CREATE TABLE `', @tb1, '` (`id` int(11) NOT NULL,`up` mediumint(9) ' at line 8

EDIT // Working now

    DROP PROCEDURE daily_backup;
    DELIMITER |
    CREATE PROCEDURE daily_backup()
    BEGIN
      SET @tbl = CONCAT('items_data_', DATE_FORMAT(CURDATE(), '%Y%m%d'));
      SET @s = CONCAT('DROP TABLE IF EXISTS ', @tbl);
      PREPARE stmt FROM @s;
      EXECUTE stmt;

      SET @s = CONCAT('CREATE TABLE `', @tbl, '` (`id` int(11) NOT NULL,`up` mediumint(9) NOT NULL,`down` mediumint(9) NOT NULL) ENGINE=Archive');

      PREPARE stmt FROM @s;
      EXECUTE stmt;

      SET @s = CONCAT('INSERT INTO ', @tbl, ' SELECT id,up,down FROM items;');
      PREPARE stmt FROM @s;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
    END

Answer 1

Variable assignments always need a SET command:

SET @s = CONCAT('CREATE TABLE `', @tbl, '` (`id` int(11) NOT NULL,`up` mediumint(9) NOT NULL,`down` mediumint(9) NOT NULL) ENGINE=Archive');

Note that you have @tb1 in this statement, it should be @tbl.

It's also good practice to DEALLOCATE a prepared statement after you use it i.e.

DEALLOCATE PREPARE stmt;

after the EXECUTE stmt