Question 252183 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Setting a parameter to a query inside a stored procedure doesn't work

Here is a MySQL request that works without a hitch :

DROP PROCEDURE IF EXISTS my_procedure;

DROP DATABASE IF EXISTS test_db;
CREATE DATABASE test_db;
USE test_db;
CREATE TABLE test_table (test_data varchar(255));
INSERT INTO test_db.test_table VALUES ('test_data1');
    
delimiter //

create procedure my_procedure()
begin
    SET @s = 'SELECT * from test_db.test_table';
    PREPARE stmt2 FROM @s;
    EXECUTE stmt2;
end//

delimiter ;

call my_procedure();

Setting the table using a parameter however, doesn’t work :

DROP PROCEDURE IF EXISTS my_procedure;

DROP DATABASE IF EXISTS test_db;
CREATE DATABASE test_db;
USE test_db;
CREATE TABLE test_table (test_data varchar(255));
INSERT INTO test_db.test_table VALUES ('test_data1');
    
delimiter //

create procedure my_procedure()
begin
    SET @s = 'SELECT * from ?';
    PREPARE stmt2 FROM @s;
    SET @a = 'test_db.test_table';
    EXECUTE stmt2 USING @a;
end//

delimiter ;

call my_procedure();

The MySQL documentation says to use "?" for parameters. So what is wrong here ?

Edit : Following answers, using CONCAT does not work :

DROP PROCEDURE IF EXISTS testing;

DELIMITER $$
CREATE PROCEDURE testing(IN db VARCHAR(255))
BEGIN
    # Does work
    SET @query = CONCAT('DROP DATABASE IF EXISTS ', db, ';');
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    SET @query = CONCAT('CREATE DATABASE ', db, ';');
    PREPARE stmt FROM @query;
    EXECUTE stmt;

    # Doesn't work
    # SET @query = CONCAT('DROP DATABASE IF EXISTS ', db, ';', 'CREATE DATABASE ', db, ';');
    # PREPARE stmt FROM @query;
    # EXECUTE stmt;
END$$
DELIMITER ;

CALL testing('test_db');

It looks like your post is mostly code; please add some more details. Sorry…

Tags:

2

Answers


  1. 0

    Parameter can be used in a prepared statement (both SQL and binary protocol) only for literals.

    This is also documented:

    Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so fort

    The benefit of a prepared statement is, that it will be parsed once (during prepare) and executed multiple times afterwards without overhead of parsing, creating execution plan etc. That wouldn’t be possible if a parameter marker could be used for a table or column name.

    Note: Please don’t forget to close your prepared statement with DEALLOCATE PREPARE to prevent leaking.

    Login or Signup to reply.
  2. 0

    As mentioned by @Georg Richter, ? characters can be used as parameter markers to indicate where data values are to be bound to the query.

    So This is a way to choose the table on which to perform your query :

    create procedure my_procedure()
begin
    SET @a = 'test_db.test_table';
    SET @s = CONCAT('SELECT * from ', @a);
    PREPARE stmt2 FROM @s;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;
end
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search