Stored Procedures in MySQL

A stored procedure is a group of pre-compiled SQL statements stored in the database and can be called by the application. This allows for efficient execution of frequently used operations, as the database parses and optimizes the SQL statements when the stored procedure is created rather than each time the application calls it.

Stored procedures can also accept input parameters and return multiple results, making them a powerful tool for data manipulation and management.

Creating Stored Procedures in MySQL

In MySQL, stored procedures are created using the CREATE PROCEDURE statement. The syntax for this statement is:

CREATE PROCEDURE procedure_name (param1 datatype, param2 datatype, ...)
BEGIN
    -- SQL statements
END;

For example, the following stored procedure accepts two input parameters; first_name and last_name. The stored procedure also returns a result set containing all the rows in the students table, where the first_name and last_name match the input parameters.

Calling Stored Procedure

Once a stored procedure has been created, it can be called by the application using the CALL statement. The syntax for this statement is:

CALL procedure_name (param1, param2, ...);

For example, to call the get_student_info stored procedure with the first_name and last_name input parameters, the following statement would be used.

CALL get_student_info('John', 'Smith');

Multiple Results using Stored Procedure

By using multiple SELECT statements, stored procedures can also be used to return multiple results within the stored procedure.

For example, the following stored procedure accepts a department_id input parameter and returns two result sets: one containing the department information and the other containing the list of employees in that department.

CREATE PROCEDURE get_department_info (department_id int)
BEGIN
    SELECT * FROM departments WHERE department_id = department_id;
    SELECT * FROM employees WHERE department_id = department_id;
END;

Performing Operations using Stored Procedure

Stored procedures can perform operations that cannot be expressed using a single SQL statement, such as loops and conditional logic. For example, the following stored procedure accepts a start_date and end_date input parameter and returns the number of days between the two dates.

CREATE PROCEDURE get_number_of_days (start_date date, end_date date)
BEGIN
    DECLARE num_days int;
    SET num_days = 0;
    
    WHILE start_date <= end_date DO
        SET num_days = num_days + 1;
        SET start_date = DATE_ADD(start_date, INTERVAL 1 DAY);
    END WHILE;
    
    SELECT num_days;
END;

Parameters

In MySQL, a procedure can have zero or more parameters, which are placeholders for values that are passed when the procedure is called. Parameters enable you to pass input values to the procedure and to receive output values from the procedure.

Here is an example of a simple procedure that has two input parameters and one output parameter:

CREATE PROCEDURE get_employee_name (IN emp_id INT, IN dept_id INT, OUT emp_name VARCHAR(100))
BEGIN
    SELECT name INTO emp_name
    FROM employees
    WHERE id = emp_id AND department_id = dept_id;
END;

In this example, the get_employee_name procedure takes two input parameters: emp_id and dept_id, which are used to identify the employee whose name is to be returned. The emp_name parameter is an output parameter that will be used to return the employee's name to the caller.

Passing the input parameter

To call this procedure and pass in the input parameters, you can use the following syntax:

CALL get_employee_name(123, 10, @emp_name);

This statement calls the get_employee_name procedure and passes the values 123 and 10 for the emp_id and dept_id parameters, respectively. The @emp_name variable holds the output value returned from the procedure.

Retrieving the value of the output parameter

After the procedure is called, you can use the SELECT statement to retrieve the value of the output parameter using SELECT @emp_name;. This statement will return the employee's name retrieved by the procedure.

MySQL Stored Procedure Disadvantages

There are a few potential disadvantages to using stored procedures in MySQL.

1. Non-portable: Stored procedures are typically written in a proprietary language, such as MySQL's Structured Query Language (SQL), which means they are not portable across different database systems. This can make it difficult to migrate a database to a different platform if it relies heavily on stored procedures.
2. High memory usage: Using stored procedures can also lead to increased memory and CPU usage due to the added complexity of the logic contained within them.
3. Difficult to debug: Stored procedures can be difficult to debug and maintain since they are typically more challenging to read and understand than regular SQL statements.
4. Vulnerable to attacks: Stored procedures can be vulnerable to SQL injection attacks if not properly secured, compromising the security of a database.

Lastly,

stored procedures can improve a database's performance by reducing the time and resources required to execute frequently-used SQL queries. Additionally, they can provide an additional layer of security by allowing database administrators to manage which users have access to which stored procedures. Overall, stored procedures can improve the efficiency and security of a MySQL database.

Thank you so much for reading this article, hope you found it helpful. Feel free to give feedbacks and subscribe.