Why keep SQL inside the database instead of in your application? Because some logic is shared by several programs, the website, the mobile app and the accounts team's Excel macro, and you want it written once, checked once, and run the same way every time. A stored procedure is a named block of SQL statements saved on the MySQL server and run with CALL. It can take parameters, use variables, IF conditions and loops, and handle errors.
This tutorial builds three procedures for a coaching institute's fee records, each one adding a new feature.
Setup
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(30),
batch VARCHAR(10),
total_fee DECIMAL(10,2),
paid DECIMAL(10,2) DEFAULT 0
);
CREATE TABLE payments (
payment_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
amount DECIMAL(10,2),
paid_on DATE
);
INSERT INTO students VALUES
(1,'Prakash','JAN-A',40000,10000),
(2,'Zoya','JAN-A',40000,40000),
(3,'Harsh','FEB-B',35000,0);
Procedure 1: an IN parameter that returns a result set
DELIMITER $$
CREATE PROCEDURE get_batch_dues(IN p_batch VARCHAR(10))
BEGIN
SELECT name, total_fee - paid AS due
FROM students
WHERE batch = p_batch AND paid < total_fee
ORDER BY due DESC;
END$$
DELIMITER ;
CALL get_batch_dues('JAN-A');
| name | due |
|---|---|
| Prakash | 30000.00 |
Why DELIMITER? The body contains semicolons. Without changing the client's statement terminator to $$, the mysql client would send CREATE PROCEDURE ... to the server at the first ; and you would get a syntax error. DELIMITER is a client command, not SQL; GUI tools such as Workbench accept it in scripts, and application drivers do not need it at all.
Prefixing parameters with p_ is a habit worth keeping: a parameter named batch would be confused with the column batch, and MySQL resolves the name to the parameter, silently making the WHERE always true.
Procedure 2: an OUT parameter
DELIMITER $$
CREATE PROCEDURE count_defaulters(IN p_batch VARCHAR(10), OUT p_count INT)
BEGIN
SELECT COUNT(*) INTO p_count
FROM students
WHERE batch = p_batch AND paid < total_fee;
END$$
DELIMITER ;
CALL count_defaulters('JAN-A', @n);
SELECT @n AS defaulters;
| defaulters |
|---|
| 1 |
SELECT ... INTO stores a single value in a variable. The caller passes a session variable (@n) to receive it. There is also INOUT, a parameter that carries a value in and a changed value back out.
Procedure 3: variables, IF, a transaction and an error handler
Recording a payment touches two tables and must never half-succeed, so this procedure validates the input, wraps the writes in a transaction, and rolls back if any SQL error occurs.
DELIMITER $$
CREATE PROCEDURE record_payment(
IN p_student INT,
IN p_amount DECIMAL(10,2),
OUT p_status VARCHAR(40))
BEGIN
DECLARE v_due DECIMAL(10,2);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET p_status = 'ERROR: payment not recorded';
END;
SELECT total_fee - paid INTO v_due
FROM students WHERE student_id = p_student;
IF v_due IS NULL THEN
SET p_status = 'Unknown student';
ELSEIF p_amount <= 0 OR p_amount > v_due THEN
SET p_status = CONCAT('Invalid amount, due is ', v_due);
ELSE
START TRANSACTION;
INSERT INTO payments (student_id, amount, paid_on)
VALUES (p_student, p_amount, CURRENT_DATE);
UPDATE students SET paid = paid + p_amount WHERE student_id = p_student;
COMMIT;
SET p_status = CONCAT('OK, remaining due ', v_due - p_amount);
END IF;
END$$
DELIMITER ;
Three calls, three outcomes:
CALL record_payment(1, 15000, @s); SELECT @s AS status;
CALL record_payment(1, 50000, @s); SELECT @s AS status;
CALL record_payment(9, 1000, @s); SELECT @s AS status;
| call | status |
|---|---|
| Prakash pays 15000 | OK, remaining due 15000.00 |
| Prakash pays 50000 | Invalid amount, due is 15000.00 |
| Student 9 | Unknown student |
SELECT student_id, name, paid FROM students;
| student_id | name | paid |
|---|---|---|
| 1 | Prakash | 25000.00 |
| 2 | Zoya | 40000.00 |
| 3 | Harsh | 0.00 |
Only the valid payment was stored. Rules to notice: DECLARE statements must come first in a BEGIN ... END block (variables, then handlers); an EXIT handler leaves the block after running, while a CONTINUE handler carries on with the next statement. Without the handler, an error between the INSERT and UPDATE would leave the transaction open with a half-recorded payment (see COMMIT and ROLLBACK in MySQL).
Calling a procedure from application code
# Python, mysql-connector-python
cur = conn.cursor()
result = cur.callproc("record_payment", (1, 5000, ""))
print(result[2]) # OUT parameter value, e.g. 'OK, remaining due 10000.00'
Parameters are passed separately from the SQL text, so a procedure call is as safe from SQL injection as a prepared statement, provided the procedure itself does not build dynamic SQL from its inputs.
Managing procedures
SHOW PROCEDURE STATUS WHERE Db = DATABASE();
SHOW CREATE PROCEDURE record_payment;
DROP PROCEDURE IF EXISTS get_batch_dues;
MySQL has no CREATE OR REPLACE PROCEDURE, and ALTER PROCEDURE changes only characteristics such as the comment, not the body. To change the code, drop and re-create it. Keep the source in Git like any other code. Running a procedure needs the EXECUTE privilege; creating one needs CREATE ROUTINE.
When not to use a stored procedure
- Complex business rules that change every sprint: they are harder to unit-test, debug and review inside MySQL than in application code.
- When you might move to another database: MySQL's procedure language is not portable to PostgreSQL or SQL Server.
- When you need a value inside a query: use a stored function instead. The difference is covered in MySQL functions vs stored procedures.
Procedures pair naturally with triggers (logic that fires on row changes) and scheduled events (logic that fires on a timer).