CODEWITHSUNDEEP
mysqlsqlstored-proceduresprepared-statement
xobicvap
xobicvap

Reputation: 302

mysql stored procedure oddity

I learned today through this section of the MySQL documentation that prepared statements cannot be performed in stored functions, but, as of MySQL version 5.0.13, they can be performed in stored procedures.

Today I was putting together a stored procedure and thought initially it might be interesting to try doing an INSERT statement in it as a prepared statement. However, despite this supposedly being possible (I'm using MySQL 5.5.14), the ? parameter marker in the statement string caused MySQL to throw a syntax error.

I threw a couple of simplified examples together using the same exact sort of syntax I used for the prepared INSERT statement. I'm hoping I just have a syntax error somewhere I just haven't caught. The first block, below, is the procedure that works, i.e. it uses the standard CONCAT(your query string) syntax.

DROP PROCEDURE IF EXISTS TestConc;
DELIMITER $$

CREATE Procedure TestConc()
BEGIN
  SET @sql := CONCAT('CREATE TABLE Foo (FooID INT) ENGINE = InnoDB');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

  SET @tn := 'Foo';
  SET @sql := CONCAT('INSERT INTO ', @tn, ' VALUES (5)');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END;
$$
DELIMITER ;

Upon calling the procedure with this code, the expected happens; 5 is stored in the FooID field of the newly-generated Foo table. However, if we change the lines between the two DEALLOCATE PREPARE directives to this:

SET @tn := 'Foo';
SET @sql := 'INSERT INTO ? VALUES (5)';
PREPARE stmt FROM @sql;
EXECUTE stmt USING @tn;

We get an error that tells us to check the syntax of the statement near '? VALUES (5)'.

Is it just not possible to substitute a parameter marker for a table name? I haven't tried doing something along the lines of 'SELECT ? FROM Foo' to see if this will work yet. Also, and I don't know if it's important, I've been trying this using MySQL Workbench 5.2.35 CE, rather than a command line.

I don't have any specific need to run queries as prepared statements within procedures ATM, I just want to make sure I have the syntax correct for doing so if I ever should need to.

Upvotes: 1

Views: 736

Answers (2)

Mark Byers
Mark Byers

Reputation: 839164

Is it just not possible to substitute a parameter marker for a table name?

No, it's not possible. If you ever think you need this feature, it could be a sign that you have a bad table design.

If you really need to specify the table at runtime, you can use dynamic SQL but be careful not to introduce SQL injection vulnerabilities.

Upvotes: 1

Devart
Devart

Reputation: 122042

The parameter '?' cannot be used for identifiers. Use first variant. From the reference - Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.

Upvotes: 2

Related Questions