Stored Procedures

Intermediate

Advanced Queries

Stored procedures encapsulate multi-statement logic in the DB; use IN/OUT/INOUT parameters, cursors, loops, and conditional logic to build server-side reusable routines.

Overview

A stored procedure is a named, precompiled block of SQL and procedural logic stored in the database. It can accept IN (input), OUT (output), and INOUT parameters, use variables, IF/CASE conditionals, WHILE/REPEAT/LOOP iterations, cursors to iterate result sets, and SIGNAL for error handling. Stored procedures reduce network round-trips for multi-step operations and centralise business logic in the database. However, they make application logic harder to test, version, and deploy — most modern applications prefer application-level logic with Flyway-managed stored procedure scripts. Understanding stored procedures is essential for legacy system maintenance and interview questions.

Basic stored procedure with IN/OUT parameters

A simple procedure that accepts input, performs logic, and returns a result via OUT parameter.

SQL — stored procedure with IN/OUT parameters

DELIMITER $$

CREATE PROCEDURE calculate_customer_ltv(
    IN  p_customer_id BIGINT,
    OUT p_ltv         DECIMAL(12, 2),
    OUT p_order_count INT
)
BEGIN
    -- Local variable declarations must come before logic
    DECLARE v_total_revenue DECIMAL(12, 2) DEFAULT 0.00;
    DECLARE v_count INT DEFAULT 0;

    -- Query into local variables
    SELECT SUM(total), COUNT(*)
    INTO   v_total_revenue, v_count
    FROM   orders
    WHERE  customer_id = p_customer_id
      AND  status NOT IN ('CANCELLED', 'REFUNDED');

    -- Handle NULL (no orders)
    SET p_ltv         = COALESCE(v_total_revenue, 0.00);
    SET p_order_count = COALESCE(v_count, 0);
END$$

DELIMITER ;

-- Call the procedure
CALL calculate_customer_ltv(42, @ltv, @orders);
SELECT @ltv AS lifetime_value, @orders AS total_orders;

Cursor iteration and DECLARE CONTINUE HANDLER

Cursors iterate over a result set row by row. Use DECLARE CONTINUE HANDLER FOR NOT FOUND to detect when iteration is complete.

SQL — cursor iteration with CONTINUE HANDLER

DELIMITER $$

CREATE PROCEDURE recalculate_all_customer_tiers()
BEGIN
    DECLARE v_done       INT DEFAULT 0;
    DECLARE v_cust_id    BIGINT;
    DECLARE v_revenue    DECIMAL(12, 2);
    DECLARE v_new_tier   VARCHAR(20);

    -- Declare cursor
    DECLARE cur CURSOR FOR
        SELECT customer_id, SUM(total) AS revenue
        FROM orders
        WHERE status = 'COMPLETED'
        GROUP BY customer_id;

    -- Handler: set v_done=1 when cursor exhausted
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO v_cust_id, v_revenue;
        IF v_done = 1 THEN
            LEAVE read_loop;
        END IF;

        -- Business logic per row
        SET v_new_tier = CASE
            WHEN v_revenue >= 10000 THEN 'PLATINUM'
            WHEN v_revenue >=  2000 THEN 'GOLD'
            WHEN v_revenue >=   500 THEN 'SILVER'
            ELSE 'BRONZE'
        END;

        UPDATE customers SET tier = v_new_tier WHERE id = v_cust_id;
    END LOOP;

    CLOSE cur;
END$$

DELIMITER ;

Error handling with SIGNAL and transaction control

SIGNAL raises a custom error. Stored procedures can manage transactions internally using DECLARE EXIT HANDLER for SQLEXCEPTION.

SQL — SIGNAL error handling + transaction in procedure

DELIMITER $$

CREATE PROCEDURE transfer_funds(
    IN p_from_account BIGINT,
    IN p_to_account   BIGINT,
    IN p_amount       DECIMAL(10, 2)
)
BEGIN
    DECLARE v_balance DECIMAL(10, 2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;  -- re-throw to caller
    END;

    IF p_amount <= 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Transfer amount must be positive';
    END IF;

    START TRANSACTION;

    -- Check balance
    SELECT balance INTO v_balance FROM accounts WHERE id = p_from_account FOR UPDATE;

    IF v_balance < p_amount THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;

    UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_account;
    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_account;

    COMMIT;
END$$

DELIMITER ;

Key Points to Remember

1Use DELIMITER $$ to avoid ; conflicts when defining multi-statement procedures in MySQL CLI.
2DECLARE CONTINUE HANDLER FOR NOT FOUND is required to detect cursor exhaustion — without it the loop runs indefinitely.
3Stored procedures do not reduce query parsing overhead in MySQL (unlike some other databases) — benefit is fewer round-trips.
4SIGNAL SQLSTATE '45000' raises a user-defined exception; RESIGNAL re-throws the caught exception to the caller.
5Test stored procedures carefully: they run in the DB context, are harder to unit-test than application code.
6Prefer application-layer logic with Flyway-managed SP scripts in production for testability and deployment control.

Interview Questions

What is the difference between IN, OUT, and INOUT parameters in a stored procedure?

EasyAmazon

How do you handle the end-of-cursor condition in a stored procedure loop?

MediumGoogle

How do you raise and handle custom errors in a MySQL stored procedure?

MediumNetflix

When would you use a stored procedure instead of application-level logic?

MediumShopify

How do you manage transactions within a stored procedure and what happens on SQLEXCEPTION?

HardUber

Ask Aria about Stored Procedures

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Common Table Expressions (CTE)

Triggers & User-Defined Functions