What is the workaround for using dynamic SQL in a stored Procedure

There is no good workaround for the absense of Dynamic SQL in MySQL functions, just klunky cludges. Some things still remain downright impossible to cludge, such as using a dynamically-calculated field name or table name in a SQL query. Yes, once in a while there is a need for doing this sort of thing!

And don't try cheat by putting the Dynamic SQL in a stored procedure and wrapping in a function or trigger, as the question poser tried - MySQL is too clever and will give you the usual obscure error message. Believe me, I have been around all the houses.

Coming from an Oracle PL/SQL and MS SQL Server background, I sorely miss the richness that PL/SQL and (to a small extent) T-SQL offers for writing procedural SQL.

Within the procedure definition, you need to store all your IN/OUT variables.

Change:

CREATE PROCEDURE `lms`.`leads_to_bak` ()

to:

CREATE PROCEDURE `lms`.`leads_to_bak` (    IN table1 varchar(32),    IN table2 varchar(32),)

Then call doing this:

CALL `lms`.`leads_to_bak`('table1', 'table2')

replacing the strings with your own.

The purpose of using stored procedures is to prevent SQL injection using strictly typed data. You don't technically need to prepare it in the stored procedure if you ONLY send strictly typed input variables in the parameter list.

This way, you handle the string operations prior to the stored procedure call. Keep your stored procs skinny!

Here's an example of one of my stored procedures:

DELIMITER ;DROP PROCEDURE IF EXISTS `save_player`;DELIMITER //CREATE PROCEDURE `save_player` (IN uid int(15) UNSIGNED,IN email varchar(100),IN name varchar(100),IN passwd char(96),IN state ENUM('active','suspended','deleted'),IN user_role ENUM('gamemaster','moderator','player'),IN locale ENUM('en','fr'),IN lvl tinyint(1),IN hp bigint(20),IN reborn tinyint(1),IN cross_ref varchar(12),IN email_verified tinyint(1),OUT new_id  int(15) UNSIGNED)BEGIN   DECLARE date_deleted timestamp DEFAULT NULL;   IF uid > 0 AND EXISTS (SELECT id FROM user WHERE `id`= uid) THEN      IF state = 'deleted' THEN        SET date_deleted = CURRENT_TIMESTAMP;      END IF ;      UPDATE `user` SET        `email` = email,        `name` = name,        `passwd` = passwd,        `state` = state,        `user_role` = user_role,        `locale` = locale,        `lvl` = lvl,        `hp` = hp,        `reborn` = reborn,        `cross_ref` = cross_ref,        `email_verified` = email_verified,        `date_deleted` = date_deleted      WHERE `id` = uid;      SET new_id = uid;   ELSE      INSERT INTO user (`email`, `name`, `passwd`, `state`, `user_role`, `locale`, `lvl`, `hp`, `reborn`, `cross_ref`, `email_verified`, `date_created`)             VALUES (email, name, passwd, state, user_role, locale, lvl, hp, reborn, cross_ref, email_verified, NOW());      SELECT LAST_INSERT_ID()  INTO new_id;   END IF; END //DELIMITER ;