Stored Procedures — MySQL

MySQL Stored Procedures

A stored procedure is a named block of SQL statements saved in the database and executed on demand with a CALL statement. Procedures encapsulate business logic on the database server, reduce network round-trips, and can be granted execution rights independently of direct table access.

Why Use Stored Procedures?

Reduce network traffic — one CALL replaces dozens of individual queries
Centralize business logic — changes apply immediately for all applications
Security — grant EXECUTE on a procedure without exposing underlying tables
Performance — procedure plans can be cached by the server
Encapsulation — hide schema complexity behind a simple interface

The DELIMITER Command

MySQL uses ; to end statements. Inside a procedure body you also need ; to end each SQL statement. To avoid the MySQL client treating each inner ; as the end of the whole CREATE PROCEDURE, you temporarily change the delimiter:

Bash

-- In the MySQL CLI or Workbench, change delimiter before CREATE PROCEDURE
DELIMITER //

CREATE PROCEDURE my_proc()
BEGIN
  SELECT 'hello';
END //

DELIMITER ;

Note

The DELIMITER command is a client-side instruction — it is not sent to the MySQL server. Application drivers (PHP PDO, Python mysql-connector, Node mysql2) handle multi-statement natively and do not need DELIMITER.

Basic Stored Procedure

SQL

DELIMITER //

CREATE PROCEDURE get_active_customers()
BEGIN
  SELECT
    customer_id,
    first_name,
    last_name,
    email
  FROM customers
  WHERE status = 'active'
  ORDER BY last_name;
END //

DELIMITER ;

-- Execute the procedure
CALL get_active_customers();

IN, OUT, and INOUT Parameters

Mode	Direction	Use Case
IN	Caller passes value in; procedure cannot change the caller's variable	Passing filter criteria, IDs, config values
OUT	Procedure sets the value; caller reads it after CALL	Returning a single computed result
INOUT	Caller passes value in; procedure can modify it; caller reads back	Accumulating totals, modifying a running value

SQL

DELIMITER //

-- IN parameter: filter orders by customer
CREATE PROCEDURE get_customer_orders(IN p_customer_id INT)
BEGIN
  SELECT order_id, order_date, total_amount, status
  FROM orders
  WHERE customer_id = p_customer_id
  ORDER BY order_date DESC;
END //

-- OUT parameter: return order count
CREATE PROCEDURE count_orders(
  IN  p_customer_id INT,
  OUT p_order_count INT
)
BEGIN
  SELECT COUNT(*) INTO p_order_count
  FROM orders
  WHERE customer_id = p_customer_id;
END //

-- INOUT parameter: apply a discount rate and return the new price
CREATE PROCEDURE apply_discount(
  IN     p_discount_pct DECIMAL(5,2),
  INOUT  p_price        DECIMAL(10,2)
)
BEGIN
  SET p_price = p_price * (1 - p_discount_pct / 100);
END //

DELIMITER ;

-- Calling with IN
CALL get_customer_orders(42);

-- Calling with OUT — use a user variable to capture result
CALL count_orders(42, @order_count);
SELECT @order_count;

-- Calling with INOUT
SET @price = 99.99;
CALL apply_discount(10, @price);
SELECT @price;  -- returns 89.99

Variables with DECLARE

SQL

DELIMITER //

CREATE PROCEDURE calculate_order_total(IN p_order_id INT)
BEGIN
  -- Declare local variables (must come first in BEGIN block)
  DECLARE v_subtotal    DECIMAL(10,2) DEFAULT 0;
  DECLARE v_tax_rate    DECIMAL(5,4)  DEFAULT 0.0875;
  DECLARE v_tax         DECIMAL(10,2);
  DECLARE v_total       DECIMAL(10,2);

  -- Calculate subtotal from order items
  SELECT SUM(quantity * unit_price)
  INTO   v_subtotal
  FROM   order_items
  WHERE  order_id = p_order_id;

  -- Calculate tax and total
  SET v_tax   = v_subtotal * v_tax_rate;
  SET v_total = v_subtotal + v_tax;

  -- Return result
  SELECT
    p_order_id   AS order_id,
    v_subtotal   AS subtotal,
    v_tax        AS tax,
    v_total      AS total;
END //

DELIMITER ;

IF / ELSEIF / ELSE

SQL

DELIMITER //

CREATE PROCEDURE categorize_customer(
  IN  p_customer_id INT,
  OUT p_category    VARCHAR(20)
)
BEGIN
  DECLARE v_total_spent DECIMAL(10,2);

  SELECT SUM(total_amount)
  INTO   v_total_spent
  FROM   orders
  WHERE  customer_id = p_customer_id
    AND  status = 'completed';

  IF v_total_spent IS NULL THEN
    SET p_category = 'New';
  ELSEIF v_total_spent < 500 THEN
    SET p_category = 'Bronze';
  ELSEIF v_total_spent < 2000 THEN
    SET p_category = 'Silver';
  ELSEIF v_total_spent < 10000 THEN
    SET p_category = 'Gold';
  ELSE
    SET p_category = 'Platinum';
  END IF;
END //

DELIMITER ;

CALL categorize_customer(42, @cat);
SELECT @cat;

WHILE Loop

SQL

DELIMITER //

CREATE PROCEDURE generate_date_series(
  IN p_start DATE,
  IN p_end   DATE
)
BEGIN
  DECLARE v_current DATE;
  SET v_current = p_start;

  CREATE TEMPORARY TABLE IF NOT EXISTS date_series (dt DATE);
  TRUNCATE TABLE date_series;

  WHILE v_current <= p_end DO
    INSERT INTO date_series VALUES (v_current);
    SET v_current = DATE_ADD(v_current, INTERVAL 1 DAY);
  END WHILE;

  SELECT dt FROM date_series ORDER BY dt;
END //

DELIMITER ;

CALL generate_date_series('2024-01-01', '2024-01-07');

REPEAT Loop

SQL

DELIMITER //

CREATE PROCEDURE repeat_example()
BEGIN
  DECLARE v_counter INT DEFAULT 1;

  REPEAT
    INSERT INTO log_table (message, created_at)
    VALUES (CONCAT('Iteration ', v_counter), NOW());
    SET v_counter = v_counter + 1;
  UNTIL v_counter > 5
  END REPEAT;
END //

DELIMITER ;

LOOP with LEAVE

SQL

DELIMITER //

CREATE PROCEDURE loop_example()
BEGIN
  DECLARE v_i INT DEFAULT 0;

  my_loop: LOOP
    SET v_i = v_i + 1;

    IF v_i > 10 THEN
      LEAVE my_loop;   -- Exit the named loop
    END IF;

    IF MOD(v_i, 2) = 0 THEN
      ITERATE my_loop; -- Skip even numbers (like 'continue')
    END IF;

    INSERT INTO odd_numbers VALUES (v_i);
  END LOOP my_loop;
END //

DELIMITER ;

Error Handling with DECLARE HANDLER

Stored procedures can catch SQL exceptions and warnings using DECLARE ... HANDLER:

SQL

DELIMITER //

CREATE PROCEDURE safe_insert_customer(
  IN  p_email     VARCHAR(255),
  IN  p_first     VARCHAR(100),
  IN  p_last      VARCHAR(100),
  OUT p_result    VARCHAR(100)
)
BEGIN
  -- Declare a handler for duplicate key violations (SQLSTATE '23000')
  DECLARE EXIT HANDLER FOR SQLSTATE '23000'
  BEGIN
    SET p_result = 'ERROR: Email already exists';
  END;

  -- Declare a general handler for any SQL exception
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET p_result = 'ERROR: Unexpected database error';
    ROLLBACK;
  END;

  START TRANSACTION;

  INSERT INTO customers (email, first_name, last_name, status, created_at)
  VALUES (p_email, p_first, p_last, 'active', NOW());

  COMMIT;
  SET p_result = 'SUCCESS';
END //

DELIMITER ;

CALL safe_insert_customer('jane@example.com', 'Jane', 'Doe', @res);
SELECT @res;

Tip

Use CONTINUE HANDLER to log the error and keep going, or EXIT HANDLER to stop the procedure block immediately. Always pair EXIT HANDLERs that catch exceptions inside transactions with a ROLLBACK.

SHOW and Manage Procedures

SQL

-- List all stored procedures in the current database
SHOW PROCEDURE STATUS WHERE Db = DATABASE()G

-- Show the CREATE PROCEDURE source
SHOW CREATE PROCEDURE calculate_order_totalG

-- Query information_schema
SELECT ROUTINE_NAME, ROUTINE_TYPE, CREATED, LAST_ALTERED
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = DATABASE()
  AND ROUTINE_TYPE   = 'PROCEDURE';

-- Drop a procedure
DROP PROCEDURE IF EXISTS get_active_customers;

Practical Example: Order Placement Procedure

SQL

DELIMITER //

CREATE PROCEDURE place_order(
  IN  p_customer_id  INT,
  IN  p_product_id   INT,
  IN  p_quantity     INT,
  OUT p_order_id     INT,
  OUT p_message      VARCHAR(200)
)
BEGIN
  DECLARE v_stock      INT;
  DECLARE v_price      DECIMAL(10,2);
  DECLARE v_total      DECIMAL(10,2);

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET p_message = 'Order failed due to a database error';
  END;

  -- Check stock availability
  SELECT stock_qty, unit_price
  INTO   v_stock, v_price
  FROM   products
  WHERE  product_id = p_product_id
  FOR UPDATE;

  IF v_stock < p_quantity THEN
    SET p_message = 'Insufficient stock';
    LEAVE place_order;    -- Exit the procedure (named via BEGIN label)
  END IF;

  SET v_total = v_price * p_quantity;

  START TRANSACTION;

  -- Create order
  INSERT INTO orders (customer_id, order_date, total_amount, status)
  VALUES (p_customer_id, NOW(), v_total, 'pending');

  SET p_order_id = LAST_INSERT_ID();

  -- Add order item
  INSERT INTO order_items (order_id, product_id, quantity, unit_price)
  VALUES (p_order_id, p_product_id, p_quantity, v_price);

  -- Deduct stock
  UPDATE products
  SET stock_qty = stock_qty - p_quantity
  WHERE product_id = p_product_id;

  COMMIT;
  SET p_message = CONCAT('Order ', p_order_id, ' placed successfully');
END //

DELIMITER ;

-- Usage
CALL place_order(42, 7, 3, @oid, @msg);
SELECT @oid AS order_id, @msg AS message;

Warning

Stored procedures run entirely on the database server. If your application framework (e.g., an ORM) does not support CALL or multi-result-set procedures, you may need to use raw SQL execution modes.

Best Practices

Prefix parameter names (e.g. p_) and local variables (e.g. v_) to avoid column name collisions
Always declare EXIT HANDLERs for SQLEXCEPTION inside transactions to prevent partial commits
Keep procedures focused — one procedure per logical business operation
Version-control your procedures in .sql migration files alongside schema changes
Use SHOW PROCEDURE STATUS regularly to audit procedures that may be orphaned or outdated
Avoid heavy loops inside procedures — prefer set-based SQL whenever possible