7

I am setting a variable, and then I want to use the USE command to change the database. Unfortunately USE doesn't evaluate the variable.

SET @con="testDB";
Query OK, 0 rows affected (0.00 sec)
select @con;
+--------+
| @con   |
+--------+
| testDB |
+--------+
1 row in set (0.00 sec)
use @con;
ERROR 1049 (42000): Unknown database '@con'

So the value of the variable is not evaluated when I try to connect. Any ideas?

bioShark
  • 683
  • 8
  • 15
  • I think you want ":=" instead of "=" when using SET – keyser Mar 29 '12 at 08:35
  • It's the same if I use := or just =, meaning USE still tries to evaluate the string @con – bioShark Mar 29 '12 at 08:37
  • Yup. Just found this: "You can also assign a value to a user variable in statements other than SET. In this case, the assignment operator must be := and not = because the latter is treated as the comparison operator = in non-SET statements: ". Sorry I can't help though. – keyser Mar 29 '12 at 08:41
  • thx for trying. Hopefully somebody has come across this issue before. – bioShark Mar 29 '12 at 08:45
  • 1
    `USE` takes an identifier. That it works with a string literal is an undocumented quirk and doesn't imply it will take a variable (or, indeed, any expression). In short, it just don't work that way. – outis Mar 29 '12 at 09:18
  • you are right. However, I found a workaround. I have answered my own question if somebody is interested. – bioShark Mar 29 '12 at 14:33

2 Answers2

10

I have found my own solution, so I am going to answer my own question, in case anybody is interested.

@outis you are right, it's not possible to use a variable with the command USE.

However, due to the fact that I want to create a table in a database specified at runtime, my solution would be to use dynamic SQL:

set @schema="testDB";
set @front="CREATE TABLE IF NOT EXISTS ";
set @endpart=".`TEST1` (DIM_ID INT(16)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;";
set @stat=concat(@front,@schema,@endpart);
prepare command from @stat;
execute command;

So basically this solution builds up the statement, prepares it and executes it. @schema parameter can even be past down to the script. This way I dynamically build the create statement.

bioShark
  • 683
  • 8
  • 15
0

I don't think it's possible to USE @var; in some way. But in some cases doing it the other way around might be an option.

USE testDB;
SET @schema = DATABASE();
MyGGaN
  • 1,766
  • 3
  • 30
  • 41