MySQL Stored Procedures Tutorial

Stored procedures are precompiled SQL programs stored inside the database. They encapsulate business logic on the server, reduce network round trips, and provide a consistent interface for complex operations. Once created, any authorized user or application can call a procedure without knowing its internal implementation.

Creating a Basic Procedure

The DELIMITER command changes the statement terminator so MySQL does not interpret semicolons inside the procedure body as the end of the CREATE PROCEDURE statement.

DELIMITER //

CREATE PROCEDURE GetActiveEmployees()
BEGIN
  SELECT id, first_name, last_name, department
  FROM employees
  WHERE status = 'active'
  ORDER BY last_name;
END //

DELIMITER ;

-- Call the procedure
CALL GetActiveEmployees();

Input Parameters

Parameters let you pass values into a procedure. Use IN for inputs, OUT for outputs, and INOUT for both. Most procedures use IN parameters.

DELIMITER //

CREATE PROCEDURE GetOrdersByCustomer(
  IN p_customer_id INT,
  IN p_min_total   DECIMAL(10,2)
)
BEGIN
  SELECT order_id, order_date, total
  FROM orders
  WHERE customer_id = p_customer_id
    AND total >= p_min_total
  ORDER BY order_date DESC;
END //

DELIMITER ;

CALL GetOrdersByCustomer(42, 100.00);

Output Parameters

OUT parameters return values to the caller. They are useful when you need to return a computed result rather than a result set.

DELIMITER //

CREATE PROCEDURE GetEmployeeCount(
  IN  p_department VARCHAR(50),
  OUT p_count      INT
)
BEGIN
  SELECT COUNT(*) INTO p_count
  FROM employees
  WHERE department = p_department;
END //

DELIMITER ;

CALL GetEmployeeCount('Engineering', @eng_count);
SELECT @eng_count AS engineering_headcount;

Variables and Control Flow

Declare local variables with DECLARE. MySQL supports IF...THEN...ELSE,CASE, WHILE, and LOOP for control flow inside procedures.

DELIMITER //

CREATE PROCEDURE ApplyRaise(IN p_emp_id INT)
BEGIN
  DECLARE v_years INT;
  DECLARE v_raise DECIMAL(4,2);

  SELECT TIMESTAMPDIFF(YEAR, hire_date, CURDATE())
  INTO v_years
  FROM employees WHERE id = p_emp_id;

  IF v_years >= 5 THEN
    SET v_raise = 1.10;
  ELSEIF v_years >= 2 THEN
    SET v_raise = 1.05;
  ELSE
    SET v_raise = 1.02;
  END IF;

  UPDATE employees
  SET salary = salary * v_raise
  WHERE id = p_emp_id;
END //

DELIMITER ;

Error Handling

Use DECLARE ... HANDLER to catch SQL exceptions and decide how to respond. This prevents a single error from crashing the entire procedure.

DELIMITER //

CREATE PROCEDURE SafeInsertUser(
  IN p_email VARCHAR(100),
  IN p_name  VARCHAR(50)
)
BEGIN
  DECLARE EXIT HANDLER FOR 1062
  BEGIN
    SELECT 'Duplicate email address' AS error_message;
  END;

  INSERT INTO users (email, first_name) VALUES (p_email, p_name);
  SELECT 'User created successfully' AS result;
END //

DELIMITER ;

Managing Procedures

Use SHOW PROCEDURE STATUS to list procedures and DROP PROCEDURE to remove one. Keep procedures focused on a single task and document their parameters clearly.

SHOW PROCEDURE STATUS WHERE Db = 'my_database';

DROP PROCEDURE IF EXISTS GetActiveEmployees;