Lesson 26 — Server-Side Logic

Stored Procedures

A stored procedure is a named, reusable block of SQL saved in the database. It accepts parameters, executes multiple statements, and can include conditional logic and loops.

SQL — Stored procedure with parameters 
-- MySQL stored procedure:
DELIMITER //
CREATE PROCEDURE GetLearnersByCity(
    IN  p_city   VARCHAR(50),
    OUT p_count  INT
)
BEGIN
    -- Return learner records:
    SELECT learner_id, full_name, course, mark
    FROM   learners
    WHERE  city = p_city
    ORDER  BY mark DESC;

    -- Set the output parameter:
    SELECT COUNT(*)
    INTO   p_count
    FROM   learners
    WHERE  city = p_city;
END //
DELIMITER ;

-- Call the procedure:
CALL GetLearnersByCity('Johannesburg', @learner_count);
SELECT @learner_count AS johannesburg_learner_count;
SQL — Procedure with validation 
-- Procedure with conditional logic:
DELIMITER //
CREATE PROCEDURE UpdateLearnerMark(
    IN p_learner_id INT,
    IN p_new_mark   DECIMAL(5, 2)
)
BEGIN
    DECLARE v_current_mark DECIMAL(5, 2);

    -- Validate mark range:
    IF p_new_mark < 0 OR p_new_mark > 100 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Mark must be between 0 and 100';
    END IF;

    -- Get current mark:
    SELECT mark INTO v_current_mark
    FROM learners WHERE learner_id = p_learner_id;

    IF v_current_mark IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Learner not found';
    END IF;

    -- Update:
    UPDATE learners
    SET    mark = p_new_mark
    WHERE  learner_id = p_learner_id;

    SELECT CONCAT('Mark updated from ', v_current_mark, ' to ', p_new_mark) AS message;
END //
DELIMITER ;

CALL UpdateLearnerMark(1, 82.0);
SQL — DROP and PostgreSQL 
-- DROP and LIST procedures:
DROP PROCEDURE IF EXISTS GetLearnersByCity;
SHOW PROCEDURE STATUS WHERE Db = 'fmtali';  -- MySQL

-- PostgreSQL equivalent (functions, not procedures):
CREATE OR REPLACE FUNCTION get_learners_by_city(p_city TEXT)
RETURNS TABLE(learner_id INT, full_name TEXT, mark DECIMAL) AS $$
BEGIN
    RETURN QUERY
    SELECT l.learner_id, l.full_name, l.mark
    FROM   learners l
    WHERE  l.city = p_city;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_learners_by_city('Cape Town');

Practice Task

Your Turn

Write stored procedures: (1) EnrolLearner(name, course) — inserts a learner and returns the new ID; (2) GetCourseStats(course) — returns count, average, min, max for a course; (3) MarkAttendance(learner_id, date, status) — inserts an attendance record after validating the status is one of Present/Absent/Late. Test all three with CALL.

Common Mistakes

  • DELIMITER change in MySQL — without it, the semicolons inside the procedure end the procedure definition early.
  • Forgetting DECLARE before using a variable.
  • Stored procedures are database-specific — MySQL syntax differs from PostgreSQL (functions) and SQL Server (T-SQL).
  • Business logic in stored procedures — harder to test, debug, and version control than application-layer code.

Professional Tip

Stored procedures are best used for complex multi-statement operations that benefit from running close to the data (bulk processing, complex reporting). For simple CRUD, keep logic in your application code where it is easier to test and version.

Mini Quiz

What is a stored procedure?