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

Question

The 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 ;

The 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 ;

The problem

I get a Error Code: 1336. Dynamic SQL is not allowed in stored function or trigger error message when making an INSERT which by implication would execute the trigger and the stored procedure. I am assuming the problem is the Dynamic SQL here:

PREPARE stmt FROM @sql1;
EXECUTE stmt;

I've looked around and there is a thread on stackoverflow on the problem, but no answer. Does anyone have any suggestions for a plausible workaround?

If someone downvotes a thread they could at least have the decency to explain why they don't approve. — , Sep 24 '12 at 16:02
Downvotes and upvotes are anonymous, for good reasons. We all get some random downvotes here and there. — ypercubeᵀᴹ, Sep 24 '12 at 16:05
I understand that, but if I did something wrong I would like to know what so I can improve in the future. — , Sep 24 '12 at 16:06
Yeah, there are many users that like that. There are also others that show vengeful behaviour (and because of that, many prefer silent downvoting). Or they are just lazy to explain why. — ypercubeᵀᴹ, Sep 24 '12 at 16:10
Possible duplicate of http://stackoverflow.com/questions/12575631/workaround-for-dynamic-statements-in-stored-procedures-called-from-triggers — Ross Smith II, Sep 25 '12 at 06:03

score 9 · Answer 1 · answered May 05 '15 at 18:16

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.

Daniel Li · Answer 2 · 2012-09-24T16:11:03.160

4

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 ;

edited Sep 24 '12 at 16:11

answered Sep 24 '12 at 15:56

Daniel Li

14,976
6
43
60

Thanks but how would the string operations be executed in the stored procedure wihout using `PREPARE`? That is what's causing the error> – Sep 24 '12 at 16:01
1

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. – Daniel Li Sep 24 '12 at 16:02
That is very useful. Thanks. So essentially creating the procedure with the INPUT variable parameters and then `SET` them inside the procedure without using `PREPARE` would do the same thing but not throw a dynamic sql error? – Sep 24 '12 at 16:05
1

Exactly. I'll attach an example of a stored proc I've been using. – Daniel Li Sep 24 '12 at 16:08
Let me see if I understand by using that example you gave. Do you mean something like this? http://jsfiddle.net/SU9yt/2/ – Sep 24 '12 at 16:30
You don't need the `SET`s. Just do all the `SET` operations in your code (PHP, Ruby, Python, etc.) and pass in the values through the `CALL` and you're good to go. – Daniel Li Sep 24 '12 at 16:39
Ah I was afraid of that actually. I'm trying to seperate my php from my database as much as possible. Its why I was using a stored procedure with a trigger. So it would work on its own. The code first selects a table name from table and then uses that value to `CONCAT` to an `INSERT` statement for some seamless data manipulation. The trigger has to be able to do all of this on its own. Does that make sense? – Sep 24 '12 at 16:46
I know what you mean, but handling it in your stored proc is not the way to go. You should minimize the amount of data you transfer over to the stored proc prior to the call by handling it on the PHP side first. – Daniel Li Sep 24 '12 at 16:52
Well I'm actually not sending any data over to the proc? When a row is inserted into `tableA` a trigger fires and calls the proc. The proc then uses a `SELECT` to grab a table name from a separate table (`tableB`). But that isn't the issue at all. The issue comes next. Then it uses that table name to `CONCAT` it to an `INSERT` statement that would make insertions into that table (`tableB`). The problem comes here. The only way to use that table name is to `CONCAT` it to an `INSERT` statement and then use `PREPARE` and `EXECUTE` <--- the problem. So optimizing data for proc is not my problem. – Sep 24 '12 at 18:52
`I get a Error Code: 1336. Dynamic SQL is not allowed in stored function or trigger error message when making an INSERT which by implication would execute the trigger and the stored procedure. I am assuming the problem is the Dynamic SQL here: PREPARE stmt FROM @sql1; EXECUTE stmt;` – Sep 24 '12 at 18:55

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

2 Answers2

Linked