Chapter 15: Stored Procedures & Functions
Now we’ll learn how to save reusable code inside the database itself — like writing your own mini-programs!
We have two main concepts today:
- Stored Procedures → Like mini-programs that can do multiple things (INSERT, UPDATE, SELECT, etc.)
- User-Defined Functions (UDFs) → Return a single value (like built-in functions: CONCAT, NOW, etc.)
Let’s start with Stored Procedures — they’re the most commonly used.
1. What is a Stored Procedure?
A stored procedure is a saved block of SQL code that:
- Has a name
- Can accept parameters (like function arguments)
- Can contain multiple SQL statements
- Can return result sets or output values
- Lives inside the database forever
Advantages:
- Reusability → Call it anytime with one line
- Security → Users can execute procedure without seeing underlying tables
- Performance → Pre-compiled, faster than sending many queries
- Maintainability → Change logic in one place
2. CREATE PROCEDURE – Basic Syntax
Syntax:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
DELIMITER // CREATE PROCEDURE procedure_name (parameters) BEGIN -- Your SQL statements here END // DELIMITER ; |
Why DELIMITER? Because procedures can have semicolons ; inside them — we temporarily change the statement ender to // so MySQL doesn’t get confused.
Example 1 – Simple procedure: Get all active students
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DELIMITER // CREATE PROCEDURE GetActiveStudents() BEGIN SELECT full_name, email, join_date FROM students WHERE is_active = TRUE ORDER BY join_date DESC; END // DELIMITER ; |
How to call it:
|
0 1 2 3 4 5 6 |
CALL GetActiveStudents(); |
You’ll see the list of active students — just like running the SELECT!
3. Parameters: IN, OUT, INOUT
| Type | Meaning | Example |
|---|---|---|
| IN | Input parameter (default) | Pass value to procedure |
| OUT | Output parameter (procedure sets it) | Get value back from procedure |
| INOUT | Both input and output | Modify value and get it back |
Example 2 – Procedure with IN parameter: Get students from a specific city
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DELIMITER // CREATE PROCEDURE GetStudentsByCity(IN p_city VARCHAR(50)) BEGIN SELECT full_name, email, city FROM students WHERE city = p_city ORDER BY full_name; END // DELIMITER ; |
Call it:
|
0 1 2 3 4 5 6 |
CALL GetStudentsByCity('Mumbai'); |
Example 3 – Procedure with OUT parameter: Count active students
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DELIMITER // CREATE PROCEDURE GetActiveStudentCount(OUT total INT) BEGIN SELECT COUNT(*) INTO total FROM students WHERE is_active = TRUE; END // DELIMITER ; |
Call it:
|
0 1 2 3 4 5 6 7 |
CALL GetActiveStudentCount(@active_count); SELECT @active_count AS 'Total Active Students'; |
Example 4 – INOUT parameter: Increase fees by a percentage
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
DELIMITER // CREATE PROCEDURE IncreaseFees(INOUT p_fees DECIMAL(10,2), IN percent INT) BEGIN SET p_fees = p_fees * (1 + percent / 100); END // DELIMITER ; |
Call it:
|
0 1 2 3 4 5 6 7 8 |
SET @my_fees = 6000.00; CALL IncreaseFees(@my_fees, 10); SELECT @my_fees AS 'New Fees'; -- Shows 6600.00 |
4. CALL Procedure – How to Execute
Just write:
|
0 1 2 3 4 5 6 |
CALL procedure_name(arguments); |
You can call procedures from:
- MySQL Workbench
- PHP/Python/Node.js code
- Other stored procedures
5. User-Defined Functions (UDFs) – Return a Single Value
Syntax:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DELIMITER // CREATE FUNCTION function_name (parameters) RETURNS datatype BEGIN -- Logic RETURN value; END // DELIMITER ; |
Example – Function: Calculate approximate age
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DELIMITER // CREATE FUNCTION CalculateAge(p_birthdate DATE) RETURNS INT DETERMINISTIC BEGIN RETURN FLOOR(DATEDIFF(CURDATE(), p_birthdate) / 365.25); END // DELIMITER ; |
Use it like any built-in function:
|
0 1 2 3 4 5 6 7 8 9 10 |
SELECT full_name, date_of_birth, CalculateAge(date_of_birth) AS Age FROM students; |
Note: DETERMINISTIC means same input → always same output (helps optimizer)
6. Variables, IF/CASE, Loops – Make Procedures Smarter
Variables:
|
0 1 2 3 4 5 6 7 8 |
DECLARE var_name datatype DEFAULT value; SET var_name = value; SELECT ... INTO var_name; |
IF / CASE:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
IF condition THEN ... ELSEIF condition THEN ... ELSE ... END IF; |
CASE:
|
0 1 2 3 4 5 6 7 8 9 10 |
CASE WHEN condition1 THEN ... WHEN condition2 THEN ... ELSE ... END CASE; |
Loops – WHILE / REPEAT / LOOP
|
0 1 2 3 4 5 6 7 8 |
WHILE condition DO ... END WHILE; |
Example – Procedure: Enroll student in course with fee validation
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
DELIMITER // CREATE PROCEDURE EnrollStudent( IN p_student_id INT, IN p_course_id INT, IN p_fees_paid DECIMAL(10,2), OUT p_message VARCHAR(100) ) BEGIN DECLARE v_fees DECIMAL(10,2); DECLARE v_student_exists INT DEFAULT 0; -- Check if student exists SELECT COUNT(*) INTO v_student_exists FROM students WHERE student_id = p_student_id; IF v_student_exists = 0 THEN SET p_message = 'Student does not exist!'; ELSE -- Get course fees SELECT fees INTO v_fees FROM courses WHERE course_id = p_course_id; IF p_fees_paid > v_fees THEN SET p_message = 'Fees paid exceed course fees!'; ELSE INSERT INTO enrollments (student_id, course_id, fees_paid) VALUES (p_student_id, p_course_id, p_fees_paid); SET p_message = CONCAT('Enrolled successfully! Balance due: ', (v_fees - p_fees_paid)); END IF; END IF; END // DELIMITER ; |
Call it:
|
0 1 2 3 4 5 6 7 |
CALL EnrollStudent(1, 1, 5000.00, @msg); SELECT @msg; |
That’s it for Chapter 15! 🎉 You now know how to write your own reusable code inside MySQL — procedures for actions, functions for calculations!
Homework for today (do it right now – very important!) Run these and paste the output of the final SELECTs:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
-- 1. Create function to get age DELIMITER // CREATE FUNCTION GetStudentAge(p_student_id INT) RETURNS INT DETERMINISTIC BEGIN DECLARE v_age INT; SELECT FLOOR(DATEDIFF(CURDATE(), date_of_birth) / 365.25) INTO v_age FROM students WHERE student_id = p_student_id; RETURN v_age; END // DELIMITER ; -- 2. Use the function SELECT full_name, GetStudentAge(student_id) AS Age FROM students LIMIT 5; -- 3. Create procedure with IF DELIMITER // CREATE PROCEDURE CheckFees(IN p_enrollment_id INT, OUT p_status VARCHAR(50)) BEGIN DECLARE v_paid DECIMAL(10,2); DECLARE v_total DECIMAL(10,2); SELECT fees_paid, c.fees INTO v_paid, v_total FROM enrollments e JOIN courses c ON e.course_id = c.course_id WHERE enrollment_id = p_enrollment_id; IF v_paid >= v_total THEN SET p_status = 'Fully Paid'; ELSEIF v_paid > 0 THEN SET p_status = 'Partially Paid'; ELSE SET p_status = 'Not Paid'; END IF; END // DELIMITER ; -- 4. Call it (use any enrollment_id you have, e.g., 1) CALL CheckFees(1, @status); SELECT @status AS PaymentStatus; |
Paste the results of the two SELECTs — I’ll check them!
