user2696497 user2696497 - 3 months ago 12
MySQL Question

MySQL use variable value as database name

I have a stored procedure that should create a database using the value of the input variable but instead it just creates a database using the variable name itself.

DELIMITER //
CREATE PROCEDURE proc_createdb (dbname VARCHAR(128))
BEGIN
CREATE DATABASE dbname;
END //


How can I use the value of
dbname
instead of creating a database called "dbname".

Answer
DELIMITER //
CREATE PROCEDURE proc_createdb (dbname VARCHAR(128))
BEGIN
    SET @theSQL=CONCAT('CREATE DATABASE ',dbname);
    PREPARE stmt1 from @theSQL;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;   
END //
DELIMITER ;

call proc_createdb('fred12236');
use fred12236;
drop schema fred12236;

Please see the MySQL Manual page entitled SQL Syntax for Prepared Statements. Note that they invariably use a CONCAT and User Variables (with an @) and they are flakey if used with DECLARE (local variables).

Comments