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

Question

Stored procedure

DELIMITER $$ CREATE PROCEDURE `lms`.`leads_to_bak` () BEGIN SET @table1 = (SELECT `tabler_name` FROM `sets` WHERE `on_off`=0 LIMIT 1); SET @table2 = CONCAT(@table1, '_bak'); SET @SQL1 = CONCAT('INSERT INTO ',@table2, '(', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), 'lead_id,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table2), ')', ' SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), 'lead_id,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table1), ' FROM ', @table1); PREPARE stmt FROM @sql1; EXECUTE stmt; END$$ DELIMITER ; 

Trigger

 DELIMITER $$ USE `lms`$$ CREATE TRIGGER `lms`.`after_insert_into_leads` AFTER INSERT ON `sets` FOR EACH ROW BEGIN CALL lms.leads_to_bak(); END$$ DELIMITER ; 

Problem

I get the error message Error Code: 1336. Dynamic SQL is not allowed in stored function or trigger when creating an INSERT , which by implication will execute the trigger and the stored procedure. I assume the problem is dynamic SQL here:

 PREPARE stmt FROM @sql1; EXECUTE stmt; 

I looked around and there is a problem in the stackoverflow problem, but no answer. Anyone have any suggestions on a plausible workaround?

Answer 1

There is no good workaround for the lack of dynamic SQL in MySQL functions, just cllunky cludges. Some things still remain completely impossible, for example, using a dynamically calculated field name or a table name in an SQL query. Yes, such things need to be done from time to time!

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

Based on the background of Oracle PL / SQL and MS SQL Server, I really miss the wealth that PL / SQL and (to a small extent) offer T-SQL for writing procedural SQL.

Answer 2

In the procedure definition, you need to save all your IN/OUT variables.

Edit:

 CREATE PROCEDURE `lms`.`leads_to_bak` () 

in

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

Then call this:

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

replacing the lines with yours.

The purpose of using stored procedures is to prevent SQL injection using strongly typed data. Technically, you don’t need to prepare it in a stored procedure if you ONLY send strongly typed input variables to the parameter list.

This way you process string operations before calling the stored procedure. Keep your saved props skinny!

Here is 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 ;