How do I pass a variable to a MySQL script?

You can pass a variable to a MySQL script using session variable. First you need to set a session variable using SET command. After that you need to pass that variable to a MySQL script.

The syntax is as follows −

First Step: Use of Set command.

SET @anyVariableName − = ’yourValue’;

Second Step: Pass a variable to a MySQL script.

UPDATE yourTableName SET yourColumnName1 = yourColumnName1+integerValue WHERE yourColumnName2 = @anyVariableName;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table Employee_Information    -> (    -> EmployeeId int NOT NULL AUTO_INCREMENT,    -> EmployeeName varchar(20) NOT NULL,    -> EmployeeSalary int,    -> EmployeeStatus varchar(20),    -> PRIMARY KEY(EmployeeId)    -> ); Query OK, 0 rows affected (0.53 sec)

Now you can insert some records in the table using insert command. The query is as follows −

mysql> insert into Employee_Information(EmployeeName,EmployeeSalary,EmployeeStatus) values('Sam',17650,'FullTime'); Query OK, 1 row affected (0.13 sec) mysql> insert into Employee_Information(EmployeeName,EmployeeSalary,EmployeeStatus) values('Carol',12000,'Trainee'); Query OK, 1 row affected (0.18 sec) mysql> insert into Employee_Information(EmployeeName,EmployeeSalary,EmployeeStatus) values('Bob',17650,'FullTime'); Query OK, 1 row affected (0.20 sec) mysql> insert into Employee_Information(EmployeeName,EmployeeSalary,EmployeeStatus) values('Mike',12000,'Trainee'); Query OK, 1 row affected (0.14 sec) mysql> insert into Employee_Information(EmployeeName,EmployeeSalary,EmployeeStatus) values('John',17650,'FullTime'); Query OK, 1 row affected (0.16 sec)

Display all records from the table using select statement.

mysql> select *from Employee_Information;

The following is the output −

+------------+--------------+----------------+----------------+ | EmployeeId | EmployeeName | EmployeeSalary | EmployeeStatus | +------------+--------------+----------------+----------------+ |          1 | Sam          |          17650 | FullTime       | |          2 | Carol        |          12000 | Trainee        | |          3 | Bob          |          17650 | FullTime       | |          4 | Mike         |          12000 | Trainee        | |          5 | John         |          17650 | FullTime       | +------------+--------------+----------------+----------------+ 5 rows in set (0.00 sec)

The following is the query to pass a variable to a MySQL script −

mysql> set @EmpStatus − = 'FullTime'; Query OK, 0 rows affected (0.03 sec) mysql> update Employee_Information set EmployeeSalary = EmployeeSalary+6500 where EmployeeStatus = @EmpStatus; Query OK, 3 rows affected (0.18 sec) Rows matched − 3 Changed − 3 Warnings − 0

Now check the table records once again using SELECT statement. I have incremented the EmployeeSalary with 6500, for the employees working FullTime.

The query is as follows −

mysql> select *from Employee_Information;

The following is the output −

+------------+--------------+----------------+----------------+ | EmployeeId | EmployeeName | EmployeeSalary | EmployeeStatus | +------------+--------------+----------------+----------------+ |          1 | Sam          |          24150 | FullTime       | |          2 | Carol        |          12000 | Trainee | |          3 | Bob          |          24150 | FullTime | |          4 | Mike         | 12000 | Trainee | |          5 | John         | 24150 | FullTime | +------------+--------------+----------------+----------------+ 5 rows in set (0.00 sec)