CODEWITHSUNDEEP
mysqlsqlmariadb
one-hand-octopus
one-hand-octopus

Reputation: 2743

How to use a variable when creating a table in mySQL?

I'm trying to declare an ENUM and use it in table creation. Here is my code:

SET @myEnum= ENUM('fulfilled', 'noshow', 'cancelled');
CREATE TABLE `Appointments` (
    `AppointmentID` VARCHAR(36) NOT NULL UNIQUE,
    `AppointmentFulfilled` @myEnum
);

But MariaDB is giving me error: '@myEnum is not valid at this position, expecting BIGINT...'

Any ideas?

Upvotes: 1

Views: 33

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

SQL syntax and identifiers must be fixed at the time the CREATE TABLE statement is parsed. Therefore you cannot use variables or parameters.

You can create a string, interpolate the variable, and then PREPARE and EXECUTE that string as dynamic SQL.

SET @myEnum= 'ENUM(''fulfilled'', ''noshow'', ''cancelled'')';

SET @ddl = CONCAT(
  'CREATE TABLE `Appointments` (
    `AppointmentID` VARCHAR(36) NOT NULL UNIQUE,
    `AppointmentFulfilled` ', @myEnum,
  ')'
);

PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Upvotes: 1

Related Questions