I need to create a database for each client that performs a particular registration, so I created a procedure to do such thing.
But I came across a small identification problem. When I run procedure it creates the database with the name I passed by parameter, but at the time of creating the tables, it creates the tables in my parent database where procedure was created, I tried to use the USE nome_do_bando to refer but did not work. follow the example code:
DELIMITER $$
USE 'loja_virtual_dev'$$
DROP PROCEDURE IF EXISTS 'cria_banco_cliente'$$
CREATE DEFINER='loja_virtual_dev'@'%' PROCEDURE 'cria_banco_cliente'(IN var_banco VARCHAR(40))
main:BEGIN
IF(var_banco <> '') THEN
SELECT var_banco AS msg;
SET @teste = CONCAT("CREATE DATABASE ",var_banco," DEFAULT CHARACTER SET latin1");
PREPARE stmt1 FROM @teste;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
-- USE 'var_banco'$$
SET @sql1 := CONCAT("CREATE TABLE IF NOT EXISTS teste_loja (
id int NOT NULL,
data date NOT NULL,
cliente int DEFAULT FALSE,
id_const int DEFAULT FALSE
) ENGINE = innoDB");
PREPARE stmt2 FROM @sql1;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt1;
LEAVE main;
END IF;
END$$
DELIMITER ;