MySQL: Хранимые процедуры и динамический SQL

Если кто-либо из вас пытался сделать вроде бы очевидную вещь, а именно, создать sql запрос внутри процедуры передав ей имя таблицы, пользователя и т.п., то скорее всего натыкались на ошибку, о том, что нельзя использовать динамический sql.

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

Такая конструкция работать не будет. А что же делать, чтобы она заработала?

Для примера напишем процедуру архивации любой таблицы в БД.
Процедура будет принимать название таблицы в качестве параметра и создавать другую таблицу с используя 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 ;

Для того чтобы составить динамический запрос нужно сначала собрать его через CONCAT() и далее выполнить используя PREPARE,EXECUTE. Такой метод очень часто применим для построения сложных запросов в процедурах. Надеюсь, кому-нибудь пригодится.

UPD: Поправил опечатки в тексте, спасибо Goganchic