MySql - Creating and executing stored procedures

Creating and Executing Stored Procedures in MySQL

Introduction to Stored Procedures in MySQL

Stored procedures are a powerful feature of MySQL that allow you to store and execute a set of SQL statements on the database server. A stored procedure is a named collection of SQL statements that can accept parameters, perform operations (such as querying or updating data), and return results. They help encapsulate complex logic, improve performance, enhance security, and promote code reuse.

In this document, we will cover the detailed concepts, syntax, best practices, and examples related to creating and executing stored procedures in MySQL using the CREATE PROCEDURE statement.

What is a Stored Procedure?

Definition

A stored procedure is a precompiled set of one or more SQL statements stored under a name and processed as a unit. Once created, it resides in the database and can be invoked (called) repeatedly by applications or users.

Why Use Stored Procedures?

• Code Reusability: Write logic once and call it multiple times.
• Improved Performance: Execution plans for stored procedures are cached, reducing parsing and compilation overhead.
• Reduced Network Traffic: Multiple SQL statements can be executed with a single call.
• Security: Permissions can be granted to execute procedures without granting direct access to underlying tables.
• Maintainability: Logic centralized in procedures simplifies changes and debugging.

Basic Syntax of CREATE PROCEDURE

General Structure

DELIMITER //
CREATE PROCEDURE procedure_name (parameters)
BEGIN
    -- SQL statements
END //
DELIMITER ;

Explanation

• DELIMITER: Changes the statement delimiter from the default semicolon (;) to something else (usually // or $$) so MySQL can recognize the procedure body correctly.
• procedure_name: The name of the stored procedure.
• parameters: Optional input, output, or input/output parameters.
• BEGIN ... END: Block defining the procedure body containing SQL statements.

Types of Parameters in Stored Procedures

Stored procedures can accept three types of parameters:

Creating Stored Procedures: Step-by-Step Examples

Example 1: Simple Stored Procedure Without Parameters

This example creates a procedure that returns all records from the employees table.

DELIMITER //

CREATE PROCEDURE GetAllEmployees()
BEGIN
    SELECT * FROM employees;
END //

DELIMITER ;

To execute this procedure, you would run:

CALL GetAllEmployees();

Example 2: Stored Procedure with IN Parameter

This procedure takes a department name as an input and returns employees belonging to that department.

DELIMITER //

CREATE PROCEDURE GetEmployeesByDepartment(IN dept_name VARCHAR(50))
BEGIN
    SELECT employee_id, name, department
    FROM employees
    WHERE department = dept_name;
END //

DELIMITER ;

Example execution:

CALL GetEmployeesByDepartment('Sales');

Example 3: Stored Procedure with OUT Parameter

This procedure counts the number of employees in a specified department and returns the count via an OUT parameter.

DELIMITER //

CREATE PROCEDURE CountEmployeesByDept(
    IN dept_name VARCHAR(50),
    OUT emp_count INT
)
BEGIN
    SELECT COUNT(*) INTO emp_count
    FROM employees
    WHERE department = dept_name;
END //

DELIMITER ;

To execute and retrieve the OUT parameter value:

CALL CountEmployeesByDept('HR', @count);
SELECT @count;

Example 4: Stored Procedure with INOUT Parameter

This example increments a given salary value by a fixed percentage and returns the new salary through an INOUT parameter.

DELIMITER //

CREATE PROCEDURE IncreaseSalary(INOUT salary DECIMAL(10,2))
BEGIN
    SET salary = salary * 1.10;
END //

DELIMITER ;

Execution example:

SET @sal = 50000;
CALL IncreaseSalary(@sal);
SELECT @sal;

Advanced Stored Procedure Concepts

Using Control Flow Statements

Stored procedures support typical programming constructs such as:

• IF ... ELSE
• WHILE loops
• REPEAT loops
• LOOP
• CASE statements

Example: Conditional Logic with IF

DELIMITER //

CREATE PROCEDURE GetEmployeeStatus(IN emp_id INT)
BEGIN
    DECLARE status VARCHAR(20);

    SELECT employment_status INTO status
    FROM employees
    WHERE employee_id = emp_id;

    IF status = 'active' THEN
        SELECT 'Employee is active.' AS message;
    ELSEIF status = 'inactive' THEN
        SELECT 'Employee is inactive.' AS message;
    ELSE
        SELECT 'Employee status unknown.' AS message;
    END IF;
END //

DELIMITER ;

Using Variables Inside Stored Procedures

You can declare local variables inside procedures with the DECLARE statement. These variables exist only during the execution of the procedure.

DECLARE var_name datatype [DEFAULT value];

Example: Using Variables

DELIMITER //

CREATE PROCEDURE GetAverageSalary(IN dept VARCHAR(50))
BEGIN
    DECLARE avg_salary DECIMAL(10,2);

    SELECT AVG(salary) INTO avg_salary
    FROM employees
    WHERE department = dept;

    SELECT avg_salary AS AverageSalary;
END //

DELIMITER ;

Executing Stored Procedures

Basic CALL Syntax

The CALL  statement is used to invoke stored procedures.

CALL procedure_name([parameters]);

Examples

• Calling a procedure without parameters:
  CALL GetAllEmployees();
• Calling with input parameters:
  CALL GetEmployeesByDepartment('IT');
• Using output parameters:
  CALL CountEmployeesByDept('Sales', @count);
  SELECT @count;

Using Stored Procedures with Programming Languages

Most programming languages (PHP, Java, Python, etc.) support calling stored procedures via database connectors or APIs, allowing the application to pass parameters and retrieve results seamlessly.

Benefits of Using Stored Procedures

1. Performance

Stored procedures are precompiled and stored on the server, so their execution is faster compared to sending multiple individual SQL queries.

2. Security

Access rights can be granted on stored procedures instead of tables, controlling what operations users can perform indirectly.

3. Maintainability

Centralizing SQL logic in procedures reduces duplication and eases maintenance.

4. Network Efficiency

Executing multiple statements in a single call reduces client-server roundtrips.

Dropping and Altering Stored Procedures

Drop a Stored Procedure

DROP PROCEDURE IF EXISTS procedure_name;

Alter Stored Procedures

MySQL does not support the ALTER PROCEDURE statement directly. To modify a stored procedure, you must drop and recreate it:

DROP PROCEDURE IF EXISTS procedure_name;

CREATE PROCEDURE procedure_name (parameters)
BEGIN
    -- new procedure body
END;

Stored procedures in MySQL are essential tools for encapsulating database logic, enhancing performance, security, and maintainability. Using CREATE PROCEDURE, you can define reusable blocks of SQL statements that accept parameters, perform conditional logic, and return results. Executing these procedures via the CALL statement integrates them into your database workflows effectively.

Mastering stored procedures allows database developers and administrators to build efficient, secure, and robust data-driven applications.