How to Use PREPARE Statement in MySQL?

While working with the MYSQL database, you may need to execute any particular command again and again or you may need to execute a command repeatedly with different values, the PREPARE statement can do that all. It allows us to prepare a statement with its appropriate name that can be executed repeatedly by utilizing the EXECUTE command. Furthermore, the PREPARE statement can be prepared with the parameters to execute a command with different values, by just changing the parameter values.

This guide will demonstrate how to use PREPARE statement in MySQL.

How to Use PREPARE Statement in MySQL?

To understand how to use the “PREPARE” statement in MySQL, take a look at the given below syntax:

PREPARE [statement_name] FROM [sql_statement];

In the above syntax, “[statement_name]” is the name of a “PREPARE” statement that will be used later for the execution of this statement. The “[sql_statement]” is the MySQL statement that will be executed whenever the “[statement_name]” is called.

To execute the query of the “PREPARE” statement, the “EXECUTE” keyword with the statement name can be utilized. The syntax of the “EXECUTE” command is provided below:

EXECUTE [statement_name];

The “[statement_name]” is the name that was defined with the PREPARE statement.

The syntax to use the “EXECUTE” command with the parameters is given below:

EXECUTE [statement_name] USING [@parameter_name];

The “USING” clause is utilized for the parameters.

Let’s head toward the examples to understand the in-depth information on how to use the PREPARE statement in MySQL.

Example 1: Prepare a Simple Statement

To prepare a simple “SELECT” statement that will fetch the data of a particular table, utilize the “PREPARE” statement, as shown below:

PREPARE lh_stmt1 FROM 'SELECT * FROM linuxhint_employees';

In the above example, the “lh_stmt1” is the statement name that will be used later for the execution of the query. The specified query will fetch all data from the “linuxhint_employees” table.

Output

The output displayed that the statement name “lh_stmt1” has been prepared.

After preparing the statement, let’s execute it by utilizing the “EXECUTE” statement followed by the statement name:

EXECUTE lh_stmt1;

Output

The output showed the table’s data by using the prepared statement name.

Example 2: Prepare a Statement With Parameters

Preparing a statement with parameters means you just need to set the statement once and then you can use that statement for multiple (different) values by just changing the variable values. Follow the provided below steps to prepare and execute the prepared statement.

Note: These steps vary on your example, for this post first a table will be created then to insert the data in that table, a PREPARE statement with parameters will be used.

Create a Table

To demonstrate the preparation of a statement with parameters, let’s begin with creating a table. For this purpose, utilize the “CREATE TABLE” command as given below in the example:

CREATE TABLE employeesData (
id INT PRIMARY KEY,
name VARCHAR(50),
salary INT
);

The table name “employeesData” with columns name “id”, “name”, and “salary” is being created.

Output

The “Query OK” after executing the “CREATE TABLE” command means that the table has been created.

Create PREPARE Statement

After creating a table, let’s prepare a statement with parameters by utilizing the “PREPARE” statement. An example of creating a prepared statement named “lh_stmt” that inserts the dynamic (depending on the values of the variables) data, into the table, is provided below:

PREPARE lh_stmt FROM 'INSERT INTO employeesData (id, name, salary) VALUES (?, ?, ?)';

In the above statement, the question mark (?) is being used as an argument for the “VALUES”.

Output

The output depicts that the PREPARE statement has been created.

Set the Variable Values

To insert the data into the table, by using the PREPARE statement, you need to set the variable for the value by using the SET keyword as given below in the example:

SET @id = 1;
SET @name = 'linuxhint';
SET @salary = 60000;

The values for the “id”, “name”, and “salary” are set in the above commands.

Output

The output showed that the values of “id”, “name”, and “salary” have been set.

Execute the PREPARE Statement

To execute the PREPARE statement with the parameters (“id”, “name”, and “salary”), utilize the EXECUTE command as shown below:

EXECUTE lh_stmt USING @id, @name, @salary;

In the above command, the parameters are utilized with the “USING” clause to set the values for the PREPARE statement. After setting the values, the “EXECUTE” keyword is utilized to execute the PREPARE statement.

Output

The output showed that the value has been inserted into the table by using the EXECUTE command.

How to Deallocate a PREPARE Statement?

To remove or deallocate a “PREPARE” statement, the “DEALLOCATE” clause followed by the “PREPARE” statement and statement name can be utilized, as shown below:

DEALLOCATE PREPARE lh_stmt;

In the above example, the “lh_stmt” is the PREPARE statement name.

Output

The output illustrates that the “PREPARE” statement has been deallocated.

Conclusion

The “PREPARE” statement in MySQL is a valuable feature that allows you to execute the same MySQL statements repeatedly with high efficiency. Moreover, it also allows you to prepare dynamic MySQL statements by preparing them with parameters, so you can create a reusable template that can be executed with different values, which makes your query execution more efficient and flexible. This guide has explained the use of PREPARE statement in MySQL.

How to Use PREPARE Statement in MySQL?