MySQL use variable value as database name

Question

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

Possible duplicate of Mysql Create Table with dynamic database name — xQbert
– xQbert, Commented Aug 22, 2016 at 18:36

2 revs · Accepted Answer · 2016-08-22 19:05:23Z

3

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

Collectives™ on Stack Overflow

MySQL use variable value as database name

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related