Priya stops writing the same query twice
Stored procedures, parameters, IF/ELSE — reusable database programs
Priya is a DBA at a Bengaluru e-commerce company. She gets the same requests every week from developers: can you give me a report of active users by city? Can you run the revenue calculation for last month?
She got tired of rewriting the same queries. She created stored procedures. Now developers call one command and get their answer.
WHAT IS A STORED PROCEDURE
A stored procedure is a named SQL program stored in the database. Instead of writing a long query every time, you call the procedure by name. It can accept inputs, run logic, and return results.
Think of it like a Python function -- but it lives inside MySQL.
CREATING YOUR FIRST PROCEDURE
DELIMITER //CREATE PROCEDURE GetUsersByCity(IN city_name VARCHAR(50))
BEGIN
SELECT id, name, email, created_at
FROM users
WHERE city = city_name
AND deleted_at IS NULL
ORDER BY created_at DESC;
END //DELIMITER ;What this does step by step:
DELIMITER // tells MySQL to use // as the end marker instead of ; so it does not confuse the semicolons inside the procedure with the end of the CREATE statement.
IN city_name VARCHAR(50) is an input parameter. The caller provides the city name.
BEGIN...END wraps the procedure body.
DELIMITER ; resets the delimiter back to normal.
CALLING A PROCEDURE
CALL GetUsersByCity('Mumbai');
CALL GetUsersByCity('Bangalore');PROCEDURE WITH OUTPUT PARAMETER
DELIMITER //CREATE PROCEDURE GetUserCount(
IN city_name VARCHAR(50),
OUT user_count INT
)
BEGIN
SELECT COUNT(*) INTO user_count
FROM users
WHERE city = city_name
AND deleted_at IS NULL;
END //DELIMITER ;-- Call it and read the output:
CALL GetUserCount('Mumbai', @count);
SELECT @count;-- @count is a session variable. CALL puts the result into it.VARIABLES INSIDE PROCEDURES
DELIMITER //CREATE PROCEDURE MonthlyRevenue(IN month_date DATE)
BEGIN
DECLARE total_revenue DECIMAL(12,2);
DECLARE order_count INT; SELECT SUM(amount), COUNT(*)
INTO total_revenue, order_count
FROM orders
WHERE status = 'paid'
AND YEAR(ordered_at) = YEAR(month_date)
AND MONTH(ordered_at) = MONTH(month_date); SELECT
total_revenue AS revenue,
order_count AS orders,
IFNULL(total_revenue / NULLIF(order_count, 0), 0) AS avg_order_value;
END //DELIMITER ;CALL MonthlyRevenue('2026-03-01');IF/ELSE IN PROCEDURES
DELIMITER //CREATE PROCEDURE CategoriseUser(IN user_id INT)
BEGIN
DECLARE total DECIMAL(12,2); SELECT COALESCE(SUM(amount), 0) INTO total
FROM orders
WHERE user_id = user_id AND status = 'paid'; IF total >= 100000 THEN
SELECT 'Platinum' AS category, total AS spent;
ELSEIF total >= 50000 THEN
SELECT 'Gold' AS category, total AS spent;
ELSEIF total >= 10000 THEN
SELECT 'Silver' AS category, total AS spent;
ELSE
SELECT 'Regular' AS category, total AS spent;
END IF;
END //DELIMITER ;CALL CategoriseUser(1);MANAGING PROCEDURES
SHOW PROCEDURE STATUS WHERE Db = 'myapp_db'; -- list all procedures
SHOW CREATE PROCEDURE GetUsersByCity; -- see the procedure code
DROP PROCEDURE IF EXISTS GetUsersByCity; -- delete itPriya now has 12 procedures that developers call directly. She writes the query once, tests it once, and it works consistently every time.
DELIMITER // is required before CREATE PROCEDURE because the body contains semicolons that would confuse MySQL
IN parameters accept input from the caller, OUT parameters return a value, INOUT do both
DECLARE creates local variables inside a procedure — they only exist during that call
SHOW CREATE PROCEDURE name shows you the full procedure code — use this to read existing procedures
Always prefix parameters with p_ to avoid name conflicts with table column names — p_user_id not user_id