MySQL: Stored Procedures and Dynamic SQL

If any of you tried to do the seemingly obvious thing, namely, to create a sql query inside the procedure by passing it the name of the table, user, etc., then you most likely came across an error that dynamic sql cannot be used.

SET @mytable='users';
SELECT * FROM @mytable;

Such a design will not work. But what to do to make it work?

For example, we will write the procedure for archiving any table in the database.
The procedure will take the table name as a parameter and create another table using engine = ARCHIVE

DELIMITER $$

DROP PROCEDURE IF EXISTS `create_archive`$$
CREATE PROCEDURE `create_archive`(IN current_table VARCHAR(50)
)
BEGIN
DECLARE template,archive_template VARCHAR(50);

-- Если название таблицы было mydata
-- то получаем название архивной таблицы mydata_20090226

SET archive_template=replace(curdate(),"-","");
SET template=CONCAT(current_table,"_",archive_template);

-- Эта конструкция формирует запрос который будет выглядить так
-- CREATE TABLE mydata_20090226 ENGINE_ARCHIVE
-- AS (SELECT * FROM mydata);

SET @archive_query:=CONCAT("CREATE TABLE ",template," ENGINE=ARCHIVE AS
(SELECT * FROM ",current_table," )");

PREPARE archive_query FROM @archive_query;
EXECUTE archive_query;
DEALLOCATE PREPARE archive_query;

END$$

DELIMITER ;

In order to compose a dynamic query, you must first collect it through CONCAT () and then execute it using PREPARE, EXECUTE. Such a method is very often applicable for constructing complex queries in procedures. I hope someone comes in handy.

UPD: Corrected typos in the text, thanks Goganchic