CODEWITHSUNDEEP
mysql
ryansin
ryansin

Reputation: 1817

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".

Upvotes: 0

Views: 1635

Answers (1)

Drew
Drew

Reputation: 24970

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).

Upvotes: 3

Related Questions