How to use a variable in create database statement

Question

I have this procedure:

DELIMITER // create DEFINER = 'root'@'localhost' procedure create_db(name TEXT) BEGIN DECLARE temp TEXT; DECLARE user TEXT; SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = name INTO temp; if temp = name then SIGNAL SQLSTATE '45002' SET MESSAGE_TEXT = 'This database already exist'; else SELECT USER() INTO user; create database name; grant all privileges on name.* to user with grant option; END IF; END // DELIMITER ; 

it works fine, it just provides literally a "name" instead of the value of the variable name. How can I say that the name is a variable? something like $ name in php or what. I look through many documents, but they all use a variable without prefixes.

Answer 1

You just need to use the prepared instructions, here is the working code:

 DELIMITER // drop procedure if exists create_db // create procedure create_db(name TEXT) BEGIN DECLARE temp TEXT; DECLARE user TEXT; SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = name INTO temp; if temp = name then SIGNAL SQLSTATE '45002' SET MESSAGE_TEXT = 'This database already exist'; else SELECT USER() INTO user; SET @s = CONCAT('CREATE DATABASE ', name); PREPARE stmt_create FROM @s; EXECUTE stmt_create; DEALLOCATE PREPARE stmt_create; SET @s = CONCAT('GRANT ALL PRIVILEGES ON ', name, '.* TO ', user, ' WITH GRANT OPTION'); PREPARE stmt_grant FROM @s; EXECUTE stmt_grant; DEALLOCATE PREPARE stmt_grant; END IF; END // DELIMITER ; 

Answer 2

I'm afraid this script is not working for me, and I would like to use it with an Azure-managed instance of MySQL 5.7. In particular, the third line gives me an error. Where exactly do I define the variables? Just replace the text with my own?

 DELIMITER // drop procedure if exists create_db // create procedure create_db(name TEXT) BEGIN DECLARE temp TEXT; DECLARE user TEXT; SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = name INTO temp; if temp = name then SIGNAL SQLSTATE '45002' SET MESSAGE_TEXT = 'This database already exist'; else SELECT USER() INTO user; SET @s = CONCAT('CREATE DATABASE ', name); PREPARE stmt_create FROM @s; EXECUTE stmt_create; DEALLOCATE PREPARE stmt_create; SET @s = CONCAT('GRANT ALL PRIVILEGES ON ', name, '.* TO ', user, ' WITH GRANT OPTION'); PREPARE stmt_grant FROM @s; EXECUTE stmt_grant; DEALLOCATE PREPARE stmt_grant; END IF; END // DELIMITER ;