CODEWITHSUNDEEP
mysqlsql
tonsih
tonsih

Reputation: 11

Creating and then using a database with a declared variable name

I'm trying to create a database named 'uppg' and then use that database once it's created. The first statement executes as it should, but nothing happens when I try to use the database with the variable. I have tried multiple variations but I don't know why it's not working.

SET @dbname = 'uppg';

SET @q1 = CONCAT(
'CREATE DATABASE `', @dbname, '`');
PREPARE stmt1 FROM @q1;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

SET @q2 = CONCAT(
'USE ', @dbname,
);

PREPARE stmt2 FROM @q2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

Upvotes: 1

Views: 31

Answers (1)

Akina
Akina

Reputation: 42632

There exists complete list of statements allowed in PREPARE: SQL Syntax Permitted in Prepared Statements.

USE is not mentioned in it. So you cannot alter current database using dynamic SQL.

The simplest solution: use complete tables names (including database name) in your queries. In this case the current DB can be any (but not none) - this won't effect your queries.

Upvotes: 2

Related Questions