MySQL: Stored Procedures and Dynamic SQL

If any of you tried to do the seemingly obvious thing, namely, create a sql query within the procedure, passing it the name of the table, user, etc., then you most likely ran into an error saying that you cannot use dynamic sql.

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

This design will not work. And what to do to make it work?

For example, let's write the procedure for archiving any table in the database.
The procedure will take the name of the table 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 compile a dynamic query, you must first build it through CONCAT () and then execute it using PREPARE, EXECUTE. Such a method is very often applicable to the construction of complex queries in procedures. I hope someone will come in handy.
')
UPD: Corrected typos in the text, thanks Goganchic