Stored Procedures

A stored procedure is a named program stored in the database and executed with CALL.

Concept

A stored procedure is a named program stored in the database and executed with CALL. MySQL stored programs (procedures, functions, triggers, events) use a SQL/PSM-style language with variables, conditionals, loops, cursors, condition handlers, and SIGNAL/RESIGNAL for errors.

Why It Matters

Procedures encapsulate multi-step operations (place an order, transfer funds, archive old test data) so they run atomically, close to the data, with fewer network round trips, and can be secured with EXECUTE privileges instead of direct table access.

Syntax

DELIMITER is a client command: it changes the statement terminator so the ; characters inside the body are not sent early. (Workbench and most GUI tools handle this automatically.)

DELIMITER //
CREATE PROCEDURE [IF NOT EXISTS] proc_name (
    [IN | OUT | INOUT] param_name data_type, ...
)
    [DETERMINISTIC | NOT DETERMINISTIC]
    [CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA]
    [SQL SECURITY {DEFINER | INVOKER}]
    [COMMENT 'text']
BEGIN
    DECLARE var_name data_type [DEFAULT value];
    -- statements
END //
DELIMITER ;

CALL proc_name(args);
DROP PROCEDURE [IF EXISTS] proc_name;
SHOW CREATE PROCEDURE proc_name;
SHOW PROCEDURE STATUS WHERE Db = 'eh_company';

Example

19.1 IN parameter returning a result set 19.2 OUT and INOUT parameters

@name are user-defined session variables; DECLAREd variables are local to the procedure block.

19.3 Variables, IF, CASE, and loops (2026: Jan 1 is a Thursday; the year has 52 full weeks plus one extra Thursday, giving 104 weekend days.)

Prefer set-based SQL over loops for data processing; loops are fine for small control logic like calendar generation.

A cursor iterates over a result set row by row (read-only, forward-only in MySQL).

Declaration order inside a block is mandatory: variables and conditions → cursors → handlers → statements.

19.5 Error handling: handlers, SIGNAL, RESIGNAL, GET DIAGNOSTICS (SELECT... INTO with no row found leaves the variables unchanged — here NULL — and raises a "No data" warning; the IS NULL check handles that.) Handler types:

DECLARE CONTINUE HANDLER FOR... Run handler, then continue after the failing statement DECLARE EXIT HANDLER FOR...

Run handler, then exit the current BEGIN...END block SQLEXCEPTION, SQLWARNING, NOT FOUND, a specific SQLSTATE 'xxxxx', a MySQL error number (e.g., 1062), or a named condition Inspecting the error inside a handler:

(Note: an INSERT into error_log after ROLLBACK is committed separately with autocommit; this is a common logging pattern.)

19.6 Dynamic SQL with prepared statements 19.7 Managing procedures

MySQL has no CREATE OR REPLACE PROCEDURE; use DROP PROCEDURE IF EXISTS then CREATE (or CREATE PROCEDURE IF NOT EXISTS, 8.0.29+).

DELIMITER //
CREATE PROCEDURE get_customer_orders(IN p_customer_id INT UNSIGNED)
BEGIN
    SELECT order_id, order_date, status, total_amount
    FROM orders
    WHERE customer_id = p_customer_id
    ORDER BY order_date;
END //
DELIMITER ;

CALL get_customer_orders(1);

+----------+------------+-----------+--------------+
| order_id | order_date | status    | total_amount |
+----------+------------+-----------+--------------+
|     1001 | 2026-01-05 | DELIVERED |     75800.00 |
|     1003 | 2026-02-02 | SHIPPED   |     18100.00 |
+----------+------------+-----------+--------------+

DELIMITER //
CREATE PROCEDURE customer_stats(
    IN  p_customer_id INT UNSIGNED,
    OUT p_orders      INT,
    OUT p_spent       DECIMAL(12,2)
)
READS SQL DATA
BEGIN
    SELECT COUNT(*), IFNULL(SUM(total_amount), 0)
      INTO p_orders, p_spent
    FROM orders
    WHERE customer_id = p_customer_id AND status <> 'CANCELLED';
END //

CREATE PROCEDURE apply_discount(INOUT p_amount DECIMAL(12,2), IN p_percent DECIMAL(5,2))
NO SQL
BEGIN
    SET p_amount = ROUND(p_amount * (1 - p_percent / 100), 2);
END //
DELIMITER ;

CALL customer_stats(2, @n, @spent);
SELECT @n AS orders, @spent AS spent;        -- 2, 105000.00

SET @amt = 18000;
CALL apply_discount(@amt, 10);
SELECT @amt;                                 -- 16200.00

DELIMITER //
CREATE PROCEDURE classify_stock()
READS SQL DATA
BEGIN
    SELECT product_name, stock,
           CASE
               WHEN stock = 0   THEN 'OUT OF STOCK'
               WHEN stock < 20  THEN 'LOW'
               ELSE 'OK'
           END AS stock_status
    FROM products
    ORDER BY stock;
END //

CREATE PROCEDURE fill_calendar(IN p_start DATE, IN p_end DATE)
MODIFIES SQL DATA
BEGIN
    DECLARE d DATE DEFAULT p_start;
    CREATE TABLE IF NOT EXISTS calendar (cal_date DATE PRIMARY KEY, is_weekend BOOLEAN NOT NULL);
    WHILE d <= p_end DO
        INSERT IGNORE INTO calendar VALUES (d, DAYOFWEEK(d) IN (1, 7));
        SET d = d + INTERVAL 1 DAY;
    END WHILE;
END //
DELIMITER ;

CALL fill_calendar('2026-01-01', '2026-12-31');
SELECT COUNT(*), SUM(is_weekend) FROM calendar;   -- 365, 104

WHILE condition DO ... END WHILE;
REPEAT ... UNTIL condition END REPEAT;
my_loop: LOOP
    IF done THEN LEAVE my_loop; END IF;      -- LEAVE = break
    IF skip THEN ITERATE my_loop; END IF;    -- ITERATE = continue
END LOOP my_loop;

DELIMITER //
CREATE PROCEDURE low_stock_report(OUT p_report TEXT)
READS SQL DATA
BEGIN
    DECLARE v_name  VARCHAR(100);
    DECLARE v_stock INT;
    DECLARE done    BOOLEAN DEFAULT FALSE;
    DECLARE cur CURSOR FOR
        SELECT product_name, stock FROM products WHERE stock < 20 ORDER BY stock;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SET p_report = '';
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_name, v_stock;
        IF done THEN LEAVE read_loop; END IF;
        SET p_report = CONCAT(p_report, v_name, '=', v_stock, '; ');
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

CALL low_stock_report(@r);
SELECT @r;       -- 'Standing Desk=0; Laptop=15; '

DELIMITER //
CREATE PROCEDURE place_order(
    IN p_order_id INT UNSIGNED, IN p_customer_id INT UNSIGNED,
    IN p_product_id INT UNSIGNED, IN p_qty INT UNSIGNED
)
MODIFIES SQL DATA
BEGIN
    DECLARE v_price DECIMAL(10,2);
    DECLARE v_stock INT;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;                                   -- re-raise the original error to the caller
    END;

    START TRANSACTION;

    SELECT price, stock INTO v_price, v_stock
    FROM products WHERE product_id = p_product_id
    FOR UPDATE;

    IF v_price IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Product does not exist';
    END IF;

    IF v_stock < p_qty THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Insufficient stock', MYSQL_ERRNO = 5001;
    END IF;

    INSERT INTO orders (order_id, customer_id, order_date, status, total_amount)
    VALUES (p_order_id, p_customer_id, CURDATE(), 'PENDING', v_price * p_qty);

    INSERT INTO order_items (order_id, product_id, quantity, unit_price)
    VALUES (p_order_id, p_product_id, p_qty, v_price);

    UPDATE products SET stock = stock - p_qty WHERE product_id = p_product_id;

   
…

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @msg = MESSAGE_TEXT;
    ROLLBACK;
    INSERT INTO error_log (proc_name, sqlstate, errno, message, logged_at)
    VALUES ('place_order', @sqlstate, @errno, @msg, NOW());
    RESIGNAL;
END;

DELIMITER //
CREATE PROCEDURE count_rows(IN p_table VARCHAR(64), OUT p_count BIGINT)
READS SQL DATA
BEGIN
    -- Allowlist identifiers: they cannot be parameters
    IF p_table NOT IN ('orders', 'customers', 'products') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Table not allowed';
    END IF;
    SET @sql = CONCAT('SELECT COUNT(*) INTO @cnt FROM `', p_table, '`');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET p_count = @cnt;
END //
DELIMITER ;

-- Values should always be bound with USING (never concatenated):
SET @status = 'PENDING';
PREPARE s FROM 'SELECT order_id FROM orders WHERE status = ?';
EXECUTE s USING @status;
DEALLOCATE PREPARE s;

SHOW PROCEDURE STATUS WHERE Db = 'eh_company';
SHOW CREATE PROCEDURE place_order\G
SELECT routine_name, routine_type, definer, security_type
FROM information_schema.routines WHERE routine_schema = 'eh_company';
DROP PROCEDURE IF EXISTS classify_stock;
GRANT EXECUTE ON PROCEDURE eh_company.place_order TO 'eh_app'@'localhost';

Expected Result

CALL customer_stats(1, @n, @s);
SELECT @n, @s;

+------+----------+
| @n   | @s       |
+------+----------+
|    2 | 93900.00 |
+------+----------+

Common Mistakes

Forgetting DELIMITER in the CLI.

Parameter names equal to column names (WHERE customer_id = customer_id is always true) — prefix parameters with p_.

Declarations in the wrong order (ERROR 1337).

Missing NOT FOUND handler in cursor loops (infinite loop or error 1329).

Catching errors without ROLLBACK/RESIGNAL.

Concatenating values into dynamic SQL (injection).

Dropped DEFINER accounts breaking procedures.

Best Practices

Prefix parameters/variables (p_, v_).

One transaction per business operation, with an EXIT handler that rolls back and re-signals.

Set-based SQL instead of cursors where possible.

Keep routine source in version control; deploy with migrations.

Use SQL SECURITY INVOKER unless the procedure is a deliberate privilege boundary.

Interview Relevance

Medium-high: "What is DELIMITER?", "IN vs OUT vs INOUT?", "How do you handle errors in MySQL procedures?", "What is SIGNAL?", "CONTINUE vs EXIT handler?", "How do cursors work?".

Practice Questions

Write get_dept_employees(IN p_dept_id) returning employees of a department.

Write raise_salary(IN p_emp_id, IN p_percent) that rejects increases above 20% with SIGNAL.

Write a procedure that archives DELIVERED orders older than a date into orders_archive and deletes them, atomically.

Rewrite a cursor loop as a single UPDATE.