Creating a table from variable names

Is it possible to create a table from a variable name? For example I have the following stored procedure that I'm trying to write but it will not compile past with the create statement.

``
CREATE DEFINER = 'sabretooth'@'%' PROCEDURE sp_archive_sabretooth`(
IN vhost INTEGER(11),
IN record_age DATETIME,
IN delete_records BOOLEAN
)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE i_exist BOOLEAN DEFAULT FALSE;
DECLARE table_to_create, backup_table_name, database_name VARCHAR(255);
DECLARE backup_tables_cursor CURSOR FOR
SELECT DISTINCT (INFORMATION_SCHEMA.COLUMNS.TABLE_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME LIKE 'vhost%'
OR
INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = 'mid')
AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA LIKE 'sabretooth%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

INSERT INTO sabretooth.debug (message) VALUES (CONCAT('Initializing sp_archive_sabretooth; vhost = ', vhost, '; record age = ', record_age, '; delete_records = ', delete_records));

SELECT INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME
INTO database_name
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME LIKE 'sabretooth%'
AND INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME <> 'sabretooth_archive';

OPEN backup_tables_cursor;

create_tables_loop: LOOP
FETCH backup_tables_cursor INTO table_to_create;
IF done THEN
LEAVE create_tables_loop;
END IF;
CREATE DATABASE IF NOT EXISTS sabretooth_archive;

SELECT CONCAT(database_name, '.', table_to_create,'_archive') INTO backup_table_name;

INSERT INTO sabretooth.debug (message) VALUES (CONCAT('I am here 38 backup_table_name = ',backup_table_name))

What you are looking for is a concept called dynamic sql. To do this, you are going to have to use prepared statements. Lucky for you, MySQL permits prepared statements in stored procedures, but not stored functions or triggers.

Also, lucky for you, MySQL permits prepared statements on CREATE TABLE commands.

So you are going to have to prepare the CREATE TABLE something like this:

SET @SQL = CONCAT('CREATE TABLE ',CONCAT('sabretooth_archive.',table_to_create),' LIKE ',CONCAT(database_name,'.',table_to_create));

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

StackExchange Database Administrators Q#14397, answer score: 6