Skip to content

MySQL procedures

Stored Procedures

Stored procedures allow you to perform complex operations directly on the database server.

Typically, when working with a database, SQL queries with SELECT, INSERT, UPDATE, and DELETE statements are used to manipulate data. If multiple applications work with the database, such SQL queries may be duplicated across different applications.

Instead of executing SQL queries from a client application, stored procedures are launched and executed on the database server itself. Each such procedure has its own name and one or more instructions that are sequentially executed by the database when called.

Creating a Stored Procedure

Consider an example of creating a stored procedure:

DELIMITER //
CREATE PROCEDURE get_all_employees()
BEGIN
    SELECT * FROM Employees;
END //
DELIMITER ;
  • DELIMITER // and DELIMITER ; are used to change the default delimiter;
  • CREATE PROCEDURE get_all_employees() creates a new procedure named get_all_employees();
  • SELECT * FROM Employees; specifies the instruction that the database will execute;
  • BEGIN and END delimit the body of the procedure.

A stored procedure can be called using the following statement: 

CALL get_all_employees;

Creating a Stored Procedure with an Input Parameter

We can also pass a parameter to stored procedures and thus narrow down the search based on some criterion. Let's look at an example:

DELIMITER //
CREATE PROCEDURE get_most_of_employees(
    IN NameFilter varchar(10)
)
BEGIN
    SELECT * FROM Employees WHERE Name=NameFilter;
END //
DELIMITER ;

The parentheses after the procedure name now contain a parameter NameFilter of type varchar(10), which will be used in the procedure body to filter queries. The IN keyword tells the database that the parameter will be provided by the user calling the procedure.

A stored procedure with a parameter can be called like this: 

CALL get_most_of_employees('John');

Deleting Stored Procedures

To delete a stored procedure, use the DROP PROCEDURE command: 

DROP PROCEDURE get_all_employees;

Advantages and Disadvantages

The advantages include the following:

  • stored procedures reduce the amount of data that needs to be transferred between the server and the client, especially during large operations;
  • the database server executes search or data filtering operations much more efficiently than the client;
  • instead of copying the same code in different programs or parts of the same program, you can simply call a single procedure.

However, there are certain disadvantages, as stored procedures:

  • are difficult to maintain;
  • are virtually impossible to debug;
  • are difficult to test;
  • are complex to modify without affecting the operation of the entire system.

Modifying the behavior of a stored procedure often requires completely stopping the system and isolating it from users. Only then will users not encounter incorrect system behavior during the procedure update process.

Stored procedures were commonly used in the past, but due to the difficulty of maintenance, testing, and deployment, most teams have moved away from them. However, many systems that rely on stored procedures still exist, so it is useful to know how to work with them.