Stored Procedures in MySQL

What Is a Stored Procedure?

A stored procedure is a named SQL routine stored by MySQL and invoked with CALL. Unlike one saved SELECT, a procedure can use a compound BEGIN ... END body, parameters, local variables, conditions, loops, handlers, multiple statements, result sets and data changes.

Verified Orders Lab

DROP TABLE IF EXISTS orders_proc_lab;
CREATE TABLE orders_proc_lab (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
  order_date DATE NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  approved_by VARCHAR(50)
) ENGINE = InnoDB;

INSERT INTO orders_proc_lab
(order_id, customer_id, status, order_date, total_amount, approved_by)
VALUES
(1,101,'PAID','2026-08-01',1200,'Admin'),
(2,101,'PENDING','2026-08-05',500,NULL),
(3,102,'PAID','2026-08-03',750,'Admin'),
(4,103,'CANCELLED','2026-08-04',300,NULL),
(5,101,'PAID','2026-08-10',1500,'Manager'),
(6,102,'PENDING','2026-08-11',900,NULL),
(7,104,'PAID','2026-08-12',2200,'Manager'),
(8,101,'PAID','2026-08-14',650,'Admin');

The lab supports read-only summaries and controlled status changes. Run procedure-creation statements with an account that has appropriate routine privileges.

Create and CALL a Procedure

DELIMITER //

CREATE PROCEDURE list_customer_orders(
  IN p_customer_id INT
)
READS SQL DATA
BEGIN
  SELECT order_id, status, order_date, total_amount
  FROM orders_proc_lab
  WHERE customer_id = p_customer_id
  ORDER BY order_date, order_id;
END //

DELIMITER ;

CALL list_customer_orders(101);

DELIMITER is a command of the mysql command-line client, not SQL stored on the server. It temporarily lets the client pass internal semicolons as part of one CREATE PROCEDURE statement. GUI and programming drivers may use a different workflow.

IN, OUT and INOUT Parameters

DELIMITER //

CREATE PROCEDURE customer_paid_summary(
  IN  p_customer_id INT,
  OUT p_paid_count INT,
  OUT p_paid_total DECIMAL(12,2)
)
READS SQL DATA
BEGIN
  SELECT COUNT(*), COALESCE(SUM(total_amount), 0)
  INTO p_paid_count, p_paid_total
  FROM orders_proc_lab
  WHERE customer_id = p_customer_id
    AND status = 'PAID';
END //

DELIMITER ;

CALL customer_paid_summary(101, @paid_count, @paid_total);
SELECT @paid_count, @paid_total;

An INOUT example can normalize or accumulate a supplied value, but do not use parameter modes to hide a confusing contract. Clear result sets or a documented output object may be easier for application code.

Local Variables, IF and Validation

DELIMITER //

CREATE PROCEDURE approve_order(
  IN p_order_id INT,
  IN p_approved_by VARCHAR(50)
)
MODIFIES SQL DATA
BEGIN
  DECLARE v_status VARCHAR(20) DEFAULT NULL;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_status = NULL;

  IF p_approved_by IS NULL OR TRIM(p_approved_by) = '' THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Approver name is required';
  END IF;

  SELECT status INTO v_status
  FROM orders_proc_lab
  WHERE order_id = p_order_id;

  IF v_status IS NULL THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Order not found';
  ELSEIF v_status = 'CANCELLED' THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Cancelled order cannot be approved';
  ELSE
    UPDATE orders_proc_lab
    SET status = 'PAID', approved_by = p_approved_by
    WHERE order_id = p_order_id;
  END IF;
END //

DELIMITER ;

DECLARE statements belong at the start of their BEGIN block before executable statements. Name parameters with a prefix such as p_ and local variables with v_ to avoid ambiguity with column names.

CALL approve_order(6, 'Principal');
SELECT order_id, status, approved_by
FROM orders_proc_lab WHERE order_id = 6;

Transactions, Handlers and Error Ownership

A procedure does not automatically create an isolated transaction. If autocommit is on, standalone DML normally commits according to session rules. A routine can contain transaction statements where permitted, but its transaction context belongs to the same session as its caller.

DELIMITER //
CREATE PROCEDURE safe_mark_paid(
  IN p_order_id INT,
  IN p_user VARCHAR(50)
)
MODIFIES SQL DATA
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;
  UPDATE orders_proc_lab
  SET status='PAID', approved_by=p_user
  WHERE order_id=p_order_id AND status='PENDING';

  IF ROW_COUNT() <> 1 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT='Pending order not found';
  END IF;
  COMMIT;
END //
DELIMITER ;

Use handlers to clean up and RESIGNAL so clients receive failure. Never swallow errors and report success.

Privileges, DEFINER and SQL SECURITY

• CREATE ROUTINE is required to create a routine.
• EXECUTE permits invocation; routine creators normally receive EXECUTE and ALTER ROUTINE automatically unless configuration changes that behavior.
• SQL SECURITY DEFINER is default and checks internal statements with definer privileges.
• SQL SECURITY INVOKER uses the caller's privileges.
• The definer should be a controlled durable account, not an employee account likely to disappear.

CREATE DEFINER = 'app_routine'@'localhost'
PROCEDURE report_orders(IN p_customer_id INT)
SQL SECURITY DEFINER
READS SQL DATA
SELECT order_id, status, total_amount
FROM orders_proc_lab
WHERE customer_id = p_customer_id;

A DEFINER routine can expose a carefully constrained operation without granting direct table access, but dynamic SQL, broad parameters or weak validation can destroy that boundary. Apply least privilege and audit the exact statements.

Inspect, Deploy, Test and Drop

SHOW CREATE PROCEDURE customer_paid_summary;
SHOW PROCEDURE STATUS
WHERE Db = DATABASE();

SELECT ROUTINE_NAME, SQL_DATA_ACCESS, SECURITY_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = DATABASE()
  AND ROUTINE_TYPE = 'PROCEDURE';

DROP PROCEDURE IF EXISTS list_customer_orders;

1. Keep each CREATE/DROP definition in source control and migration order.
2. Use explicit parameter types and validate boundaries, NULLs and missing rows.
3. Test outputs, result sets, affected rows and error SQLSTATEs.
4. Document whether the procedure owns or joins a caller transaction.
5. Test concurrent calls and locking for write routines.
6. Use indexes for internal predicates and inspect their plans.
7. Review DEFINER accounts and EXECUTE grants after restore/migration.
8. Avoid returning many unrelated result sets that drivers handle poorly.
9. Monitor latency and call frequency; stored code can still be slow.

Continue with stored functions, transactions and prepared statements.

Official References

• MySQL 8.4: CREATE PROCEDURE and FUNCTION
• MySQL 8.4: CALL Statement
• MySQL 8.4: Stored Routine Privileges

Parameter modes, invocation, characteristics, privilege behavior and routine management were checked against the official MySQL 8.4 manual.