MySQL Stored Procedures

 

A MySQL Stored Procedure is a segment of declarative SQL statements that is stored directly inside the database catalog.

Think of it as a custom function or a macro that you write once and save in your database. Instead of an application sending 10 individual SQL queries over the network to execute a complex business process, it can make a single call to the database to run the stored procedure.

Why Use Stored Procedures? (With ER Model Examples)

Using the provided database schema, here is why you would want to use stored procedures over standard queries:

1. Reducing Network Traffic

If a customer wants to enroll in a course, your application might need to:

1. Check if the session exists in sessions.
2. Check if the current headcount is less than maxPax.
3. Insert a record into enrollments.
4. Update the current status of the session if it's now full.

Instead of sending 4 separate queries back and forth between your application server and your database server, a stored procedure wraps all of this logic into ​one single call​.

2. Encapsulating Business Logic & Transactions

When data relies on strict rules (like financial accounting or inventory), you want to guarantee that either all steps succeed or all steps fail together. Stored procedures allow you to handle database Transactions (START TRANSACTION, COMMIT, ROLLBACK) safely inside the database.

3. Enhanced Security

You can restrict a user or application from directly inserting or modifying data in the enrollments or sessions tables. Instead, you grant them permission only to execute a specific stored procedure. This ensures data enters your system strictly on your terms.

Practical Examples Based on Your ER Model

Stored procedures can accept input parameters (IN) and return values via output parameters (OUT).

Example 1: Enrolling a Customer Safely (With Logic Checks)

Let's create a procedure called EnrollCustomer. It will take a customerId and a sessionId, check if the session is already full based on maxPax, and insert the enrollment only if space is available.

SQL

DELIMITER //

CREATE PROCEDURE EnrollCustomer(
    IN p_customerId INT,
    IN p_sessionId INT,
    IN p_price DECIMAL(6,2),
    OUT p_statusMessage VARCHAR(100)
)
BEGIN
    DECLARE current_pax INT;
    DECLARE max_pax INT;

    -- 1. Get the current number of enrollments for this session
    SELECT COUNT(*) INTO current_pax 
    FROM enrollments 
    WHERE sessionId = p_sessionId AND sessionStatus = 'Active';

    -- 2. Get the maximum allowed pax for this session
    SELECT maxPax INTO max_pax 
    FROM sessions 
    WHERE sessionId = p_sessionId;

    -- 3. Check if there is room available
    IF current_pax < max_pax THEN
        -- Insert the enrollment record
        INSERT INTO enrollments (customerId, sessionId, price, paymentStatus, sessionStatus, createdAt)
        VALUES (p_customerId, p_sessionId, p_price, 'Pending', 'Active', NOW());
        
        SET p_statusMessage = 'Enrollment successful!';
    ELSE
        -- Deny enrollment
        SET p_statusMessage = 'Enrollment failed: Session is fully booked.';
    END IF;
END //

DELIMITER ;
​

How you run it:

SQL

-- Call the procedure and pass a variable to catch the output message
CALL EnrollCustomer(1, 10, 150.00, @message);

-- Check the result
SELECT @message;
​

Example 2: Automatically Archiving Old Sessions (Batch Processing)

Imagine management wants a nightly script to look through the sessions table and change the status to 'Completed' for any session where the endDateTime has passed.

SQL

DELIMITER //

CREATE PROCEDURE ArchivePastSessions()
BEGIN
    UPDATE sessions
    SET status = 'Completed'
    WHERE endDateTime < NOW() AND status = 'Active';
END //

DELIMITER ;
​

How you run it:

Your application server or a MySQL Event Scheduler can trigger this with a single line every midnight:

SQL

CALL ArchivePastSessions();
​

Pros and Cons of Stored Procedures

Pros 👍	Cons 👎
**Performance:**SQL statements inside procedures are compiled and cached by the database engine, making them highly efficient.	**High CPU Usage:**Moving heavy computational logic from application servers to the database server can overwhelm your database CPU.
**Centralized Logic:**If your registration rules change, you update the procedure once in the database, and every app (Web, iOS, Android) is instantly updated.	**Hard to Debug & Version Control:**Debugging stored procedures is notoriously difficult compared to standard application languages (Python, Java, Node.js).
**Strict Security:**Shields the underlying table structures from the client application entirely.