Creating and using procedures to automate MySQL

A MySQL stored procedure contains a set of SQL statements stored separately in the MySQL database. It's a powerful feature of MySQL that enables developers to write complex SQL queries and perform repetitive tasks in a more efficient and structured manner. Stored procedures are similar to functions in programming languages and can be called and executed from other SQL statements or programs.

In terms of performance, stored procedures offer several benefits. Since the code is executed on the server side, stored procedures can reduce the amount of network traffic between the application and the database server. This can result in faster execution times and lower network latency. Additionally, stored procedures can be optimized for performance by using efficient algorithms and data structures. They can also be cached in memory, which can further improve execution times.

Storing the procedures inside the MySQL database is more secure than using ad hoc SQL queries. This is because ad hoc queries are placed inside the application file. As any unauthorized person who has access to the application source code can see the queries and spot the original table and column names, this could cause potential security issues. You can avoid this with stored procedures, since you can grant database users permission to access the stored procedures without giving them access to the tables.

In this article, we'll walk through how to create and execute stored procedures using MySQL. We'll also learn how to create a stored procedure with various parameter options. Finally, we'll see how to automate some repetitive tasks such as backup, user creation, and user permission management using MySQL stored procedures.

MySQL stored procedure syntax

Learning how to set up a stored procedure is relatively straightforward since it combines standard SQL queries and executes them together. In simple terms, we assign a name to multiple SQL queries, and when we call the stored procedure, these multiple queries are executed as a single unit.

The following is the syntax for creating a stored procedure in MySQL:

In the above code:

• CREATE PROCEDURE is the standard command for creating procedures.
• procedure_name() represents the name of the stored procedure. When naming it, we must make sure we use the naming rules of MySQL, where names are case-sensitive, need to start with a letter, and can't have any spaces.
• Parameters are optional, and we can use them to pass values to the procedure.
• All SQL queries need to be written between BEGIN and END.

Note that a semicolon (;) is the default delimiter used to separate multiple SQL queries between BEGIN and END. However, MySQL won't consider the stored procedure as a single statement if it encounters a semicolon and will try to execute queries immediately instead of saving the procedure. In other words, MySQL will try to execute the code whenever it encounters a semicolon, leading to an error when executing the stored procedure. To avoid this behavior, you can use DELIMITER $$ to instruct MySQL to consider $$ as the delimiter for the purposes of the stored procedure, then use DELIMITER ; to reset to the default delimiter after the procedure.

Creating a simple stored procedure

Using the above syntax, we can create a simple stored procedure. The following code will create a stored procedure that prints "Hello World" as an output:

In this code, sayHello() is the procedure name. The SELECT command with the single quote prints the output.

To execute the stored procedure, use the CALL command with the procedure name:

Viewing and dropping stored procedures

We can view any existing stored procedures by querying the routines table in MySQL's information_schema.

Use the following query to obtain a list of stored procedures for a specific MySQL database:

This query will return the names of the stored procedures along with their associated metadata, such as the routine type, definer, creation date, security type, and SQL data access.

If necessary, we can use the following command to drop a stored procedure:

Creating a parameterized stored procedure

In order to create a dynamic query, we need to define a placeholder within the query. This placeholder will be assigned a value when we call the stored procedure. A parameter is used to accomplish this. For instance, if we need to change a user's password, we would pass the new password as an input parameter to the stored procedure.

A MySQL stored procedure has three modes for a parameter:

• IN: If a parameter is declared as IN, it's used to accept input values, and we cannot assign a value to this parameter inside the stored procedure. We can use the IN parameter in the WHERE condition to filter data. It can also be used with the CREATE command. If we don't specify the parameter mode, the default mode is IN.
• OUT: If a parameter is declared as OUT, it's used to assign values to the parameter inside the stored procedure. We can access the assigned value of this parameter outside of the stored procedure.
• INOUT: If a parameter is declared as INOUT, we can use both input and output values for it.

The following is the syntax for adding parameters to the stored procedure:

Using that syntax, the following code will create a stored procedure that changes the password of a user and includes parameters using the IN mode:

In this code, the stored procedure is changePassword(). The ALTER command will change the password for the given username.

To execute the stored procedure, use the CALL command with the procedure name:

In this example, the stored procedure changePassword() takes two parameters: a username ('erp_user1') and a new password ('TxlbS@84SBR2').

Automating repetitive tasks using stored procedures

Repetitive tasks such as creating users, changing user passwords, making backups of table data, managing user permissions, and moving old data to secondary tables often require the same set of standard SQL queries. However, manually changing the input values every time we execute these queries can be time-consuming and tedious. To simplify these tasks, we can create stored procedures with parameters that allow us to automate these processes by passing input values to the queries at runtime. This can save us time and effort in the long run. This section highlights a few examples of repetitive tasks that can be automated by stored procedures.

Backing up a table

To back up an entire table's data in MySQL, we can use the SELECT … INTO OUTFILE command, which writes the selected data to a file. The following is the syntax for this command:

Before writing the stored procedure, we must tell MySQL where to store the output file. The default file location is var/lib/mysql-files. However, this location can be modified using the mysql.ini file if needed.

Note that MySQL will not allow us to write to the output file if the location is not properly configured. To check the configured output file location, we can use the following command:

The following example creates a stored procedure to back up table data using SELECT … INTO OUTFILE:

Execute this stored procedure using the CALL command:

Creating or removing a user

Creating and removing users in the MySQL database can also be automated using stored procedures.

We can create a user and assign it to the database in MySQL using the following command:

The following code creates a stored procedure to automate this task:

When using stored procedure parameters, we cannot use them directly inside Data Definition Language (DDL) and Data Control Language (DCL) commands like CREATE, ALTER, GRANT, and others. This is because DDL and DCL commands cannot directly consume parameter values. To work around this, we can use the CONCAT function to generate the query string with the parameter values included. We can then execute the generated query using the PREPARE and EXECUTE commands.

To use the createUser() stored procedure, pass in the username, password, and database name, and execute it using the CALL command:

Similarly, we can create a stored procedure to remove a user:

Pass the username as input and execute the stored procedure to remove the user from the database:

Granting and revoking permissions

We can also create a stored procedure to help reduce the workload around managing user permissions.

The following code creates a stored procedure to add or remove user permissions:

Execute the grantPermission() stored procedure by running the following command:

The following code will create a stored procedure to remove user permission:

Execute the revokePermission() stored procedure by running the following command:

And that's how use MySQL stored procedure. Although MySQL stored procedure has many benefits, it can be difficult to maintain and challenging to debug. Luckily, there is an alternative platform available for automating repetitive tasks easily.

Introducing Airplane: An easy way to automate repetitive tasks

Airplane is the developer platform for building custom internal tools. If you're looking for a maintenance-free, easy-to-use alternative to stored procedures for automating tasks in MySQL, consider using Airplane. With Airplane, you can build powerful internal tools using Python scripts, JavaScript functions, MySQL queries, and APIs, and create custom UIs to fit the unique needs of your business. Airplane allows you to build dynamic apps complete with enterprise-grade workflows that your support, operations, and other teams can use safely, serving your complete internal tooling needs.

To try it out and automate your first SQL-based task, sign up for a free account or book a demo.

Author: Bharathiraja Shanmugam

Bharathiraja Shanmugan is a full-stack developer with more than ten years of experience developing applications. He has created various enterprise applications from scratch using the latest JavaScript Frameworks. He has also implemented DevOps in enterprise applications and worked on many Machine Learning projects.