I have a Stored Procedure which loops through all the tables in a schema (customerinfo) and makes a backup of them by renaming them to include the date and copying them to another schema (customerinfo_bak).

That looks as follows

DELIMITER $$
CREATE PROCEDURE backup_tables()
BEGIN
  DECLARE DONE INT DEFAULT FALSE;
  DECLARE tableName VARCHAR(255);
  DECLARE cursorController CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = 'customerinfo';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE;
  
  OPEN cursorController;
  CREATE DATABASE IF NOT EXISTS customerinfo_bak;
  REPEAT
  
    SET @backup_table = CONCAT('customerinfo_bak.', tableName, '_backup_', DATE_FORMAT(CURDATE(), '%Y%m%d'));
    SET @sqlstmt = CONCAT('CREATE TABLE ', @backup_table, ' LIKE customerinfo.', tableName);
    PREPARE stmt FROM @sqlstmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET @sqlstmt = CONCAT('INSERT INTO ', @backup_table, ' SELECT * FROM customerinfo.', tableName);
    PREPARE stmt FROM @sqlstmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  
  UNTIL DONE 
  END REPEAT;
  
  CLOSE cursorController;

END$$
DELIMITER ;

The issue that I’m having is that the SP saves and is visible in my schema, but upon calling it with CALL customerinfo_bak.backup_tables(); I get the following error:

Error Code: 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 ‘NULL’ at line 1

I have a suspicion that it’s to do with how I use my Local and Session variables in the PREPARE section based on this MySQL Error 1064 : near NULL at line 1 answer from Bill Karwin, but I don’t understand why the error would appear as a NULL syntax issue.

If someone has any idea why I can’t call my SP that would be greatly appreciated.