Introduction to MySQL Stored Procedures

What is a Stored Procedure in MySQL?

A Stored Procedure in MySQL is a precompiled SQL code block that is stored in the database and can be executed multiple times. It helps reduce redundancy, improve performance, and enhance security by encapsulating SQL logic into reusable routines.

1. Advantages of Using Stored Procedures

✔ Performance Improvement – Reduces SQL parsing time since it is precompiled.
✔ Code Reusability – Write once, and use multiple times.
✔ Security – Users can execute procedures without direct table access.
✔ Reduced Network Traffic – Only procedure calls are sent, reducing data transmission.

2. Syntax of Creating a Stored Procedure

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

DELIMITER // – Changes the default delimiter to // to avoid conflicts with ;.
CREATE PROCEDURE procedure_name() – Defines the procedure.
BEGIN ... END – Contains the SQL statements.

3. Example: Creating and Executing a Stored Procedure

Step 1: Create a Simple Procedure

DELIMITER //
CREATE PROCEDURE get_total_employees()
BEGIN
    SELECT COUNT(*) AS total FROM employees;
END //
DELIMITER ;

Step 2: Execute the Procedure

CALL get_total_employees();

4. Stored Procedure with Parameters

Stored procedures can accept IN, OUT, and INOUT parameters.

DELIMITER //
CREATE PROCEDURE get_employee_by_id(IN emp_id INT)
BEGIN
    SELECT * FROM employees WHERE id = emp_id;
END //
DELIMITER ;

Execute the procedure:

CALL get_employee_by_id(101);

5. Modify or Delete a Stored Procedure

✔ Alter Procedure:

MySQL does not support ALTER PROCEDURE. Instead, drop and recreate the procedure.

✔ Drop a Procedure:

DROP PROCEDURE IF EXISTS get_total_employees;

6. Listing Stored Procedures

To see existing stored procedures:

SHOW PROCEDURE STATUS WHERE Db = 'your_database_name';

7. Error Handling in Stored Procedures

To handle errors, use DECLARE ... HANDLER:

DELIMITER //
CREATE PROCEDURE safe_procedure()
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'An error occurred';
    END;
    
    -- Your SQL logic
END //
DELIMITER ;

8. Summary

✔ Stored Procedures improve efficiency and reusability in MySQL.
✔ They can accept parameters and return results.
✔ Use CALL procedure_name() to execute a procedure.
✔ Use SHOW PROCEDURE STATUS to list stored procedures.
✔ Error handling can be managed using DECLARE HANDLER.

Would you like help implementing stored procedures in your project? 🚀

Souy Soeng

Introduction to MySQL Stored Procedures