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
CREATE PROCEDURE `lms`.`leads_to_bak` ()
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 ;