title: Stored procedure for copying data
body: <p>I am all new to creating procedures and triggers in MySQL, but have struggled with this over the last couple of days, and it simply will not work. The error messages, that I get from Mysql does not help me any longer.</p>
  
  <p>So I am trying to create a procedure, which I need to run after an update. I will take any updates and store new data in a dynamic created table in another database.
  Here it is:</p>
  
  <pre><code>CREATE PROCEDURE get_price_storage_table (IN market_id INT(11), OUT tablename VARCHAR(50))
              BEGIN
                  SET @NUMBER     = CEILING(market_id / 100000) * 100000;
                  SET tablename   = CONCAT('price_',@NUMBER);
                  SET @SQL = CONCAT('CREATE TABLE IF NOT EXISTS data.',tablename,'(
                                      `pk_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
                                      `fk_market_id` INT(11) UNSIGNED NOT NULL,
                                      `fk_outcome_type_id` MEDIUMINT(8) UNSIGNED NOT NULL,
                                      `price` DOUBLE NOT NULL,
                                      `status` ENUM(\'enabled\',\'disabled\') NOT NULL,
                                      `created` DATETIME NOT NULL,
                                      PRIMARY KEY (`pk_id`)
                                      ) ENGINE=InnoDB DEFAULT CHARSET=utf8')');
                  PREPARE stmt FROM @SQL;
                  EXECUTE stmt;
                  DEALLOCATE PREPARE stmt;
              END;
  </code></pre>
  
  <p>This need to be triggered after update, where the SQL goes like:</p>
  
  <pre><code>CREATE TRIGGER copy_price_data AFTER UPDATE ON price
          FOR EACH ROW
              BEGIN
                  IF NEW.updated &lt;&gt; OLD.updated THEN
                      SET @market_id = NEW.fk_market_id;
                      SET @tablename = NULL;
                      CALL create_price_storage_table(@market_id, @tablename);
                      SELECT @tablename;
                      SET @SQL = CONCAT(
                      'INSERT INTO ',
                      @tablename,
                      ' (`fk_market_id`, `fk_outcome_type_id`, `price`, `status`, `created`) VALUES (NEW.fk_market_id, NEW.fk_outcome_type_id, NEW.price, NEW.status, NOW())');
                      PREPARE stmt FROM @SQL;
                      EXECUTE stmt;
                      DEALLOCATE PREPARE stmt;
                  END IF;
              END;
  </code></pre>
  
  <p>When trying to create the procedure, then I get the following error message from MySQL:</p>
  
  <pre><code>CREATE PROCEDURE get_price_storage_table (IN market_id INT(11), OUT tablename VARCHAR(50))
          BEGIN
              SET @NUMBER     = CEILING(market_id / 100000) * 100000;
  </code></pre>
  
  <blockquote>
    <p>MySQL returnerede: Dokumentation 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 '' at line 3</p>
  </blockquote>
  
  <p>I hope someone with greater understanding than me, can point me in the right direction. Thanks.</p>