how do I 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 great just it supply literally "name" instead of value of variable name. How do I tell it that name is a variable? something like $name in php or that. I browser many documentations but they all are using variable with no prefixes.

Petr · Accepted Answer · 2013-03-06 15:01:16Z

6

You just need to use prepared statements, here is 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 ;

edited Mar 6, 2013 at 15:01

Petr

14.7k22 gold badges99 silver badges158 bronze badges

answered Mar 6, 2013 at 14:28

Rostyslav

4283 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how do I use a variable in create database statement

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