Composing a dynamic query in a stored procedure in MYSQL

webdevfp 0
Viewed 21

I am creating a stored procedure in MySQL in order to execute the same queries in several parts of my application.

One of the two tables, as you can guess from the code, will be different depending on the script that will execute the stored procedure. The fields of the second query will be the same in number and type. The name of the table and the name of a column will change.

In order to make a dynamic query, I have used the CONCAT() command but I don't like it very much because it contains too many fragmented parts and too many quotes. Is there a more elegant way to compose a dynamic query like this?

BEGIN

 INSERT IGNORE INTO tag (cod, tag) values (cod_tag_in, tag_in);

 SET @query = CONCAT("INSERT INTO ", table_in, " (cod, ", table_field_in, ", tag_cod) values ('", app_cod_in, "','", section_cod_in, "', (SELECT cod FROM tag WHERE tag = '", tag_in, "'))");

 PREPARE stmt FROM @query;

 EXECUTE stmt;

 DEALLOCATE PREPARE stmt;

END
stored-procedures mysql sql
1 Answers

Here is how I would write this routine if the table name and field name are really dynamic:

BEGIN

 INSERT IGNORE INTO tag (cod, tag) values (cod_tag_in, tag_in);

 SET @query = CONCAT('INSERT INTO `', table_in, '` (cod, `', table_field_in, '`, tag_cod) values (?, ?, ?)');

 SET @cod = app_cod_in, @field = section_code_in, @tag_cod = cod_tag_in;

 PREPARE stmt FROM @query;

 EXECUTE stmt USING @cod, @field, @tag_cod;

 DEALLOCATE PREPARE stmt;

END

Use query parameters so you don't have to use all those meticulous quotes for the values. This also protects you in case the values themselves contain quote characters.

I don't see the need for the subquery, since the value you just inserted in the the tag table should be the same as cod_tag_in anyway.

You do need the breaks in quotes for the dynamic table name and dynamic field name, because you can't use parameters as table or column identifiers.

I put back-ticks around the table and field name, just in case these identifiers require them (conflict with SQL reserved keywords, contain whitespace or punctuation, etc.).

However, you shouldn't even use dynamic SQL for this at all. You should use a CASE statement for the tables you need to support:

BEGIN

 INSERT IGNORE INTO tag (cod, tag) values (cod_tag_in, tag_in);

 CASE table_in 
 WHEN 'mytable1' THEN
   INSERT INTO mytable1 (cod, myfield1, tag_cod) VALUES (app_cod_in, section_code_in, cod_tag_in);
 WHEN 'mytable2' THEN
   INSERT INTO mytable2 (cod, myfield2, tag_cod) VALUES (app_cod_in, section_code_in, cod_tag_in);
 WHEN 'mytable3' THEN
   INSERT INTO mytable3 (cod, myfield3, tag_cod) VALUES (app_cod_in, section_code_in, cod_tag_in);
 ELSE
   SIGNAL SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Unknown table';
 END CASE;

END

This means you don't have to worry about CONCAT()-ing any fragments of SQL, and you don't have to worry about SQL injection risks, and you don't have to worry about table or column identifiers that have conflicting characters.

But you are limited to the tables you have coded in the CASE statement. If you are in the habit of creating new tables on the fly frequently, you'd have to replace the stored procedure with longer and longer CASE statements. But if you need to do that, I'd reconsider the design that so frequently requires new tables of similar structure.

Bill Karwin0
Related