iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

Stored Procedures

By · Reviewed & last updated · Part of MySQL Tutorial

A stored procedure in MySQL groups SQL into a named routine you call with CALL. Procedures can take IN/OUT/INOUT parameters, use local variables, control flow (IF/CASE/WHILE/LOOP), and handle errors with DECLARE HANDLER. Use them sparingly — they hide logic from version control and unit tests — but they are the right tool for performance-critical batches and admin chores.

Procedure with parameters, transactions, and error handling

EXAMPLE
-- 1) A simple procedure with input + output params
DELIMITER //

CREATE PROCEDURE place_order(
  IN  p_customer_id BIGINT,
  IN  p_total_cents BIGINT,
  OUT p_order_id    BIGINT
)
SQL SECURITY INVOKER
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;
    INSERT INTO orders (customer_id, total_cents, status, created_at)
    VALUES (p_customer_id, p_total_cents, 'new', NOW());
    SET p_order_id = LAST_INSERT_ID();

    UPDATE customers SET orders_count = orders_count + 1 WHERE id = p_customer_id;
  COMMIT;
END//

DELIMITER ;

-- Call it from SQL
CALL place_order(42, 4995, @oid);
SELECT @oid;

-- Call it from PHP (PDO)
-- $stmt = $pdo->prepare('CALL place_order(:cid, :total, @oid)');
-- $stmt->execute(['cid' => 42, 'total' => 4995]);
-- $oid = $pdo->query('SELECT @oid')->fetchColumn();

-- 2) Loops, conditional logic, and a cursor — batch backfill example
DELIMITER //
CREATE PROCEDURE backfill_slugs()
BEGIN
  DECLARE v_id  BIGINT;
  DECLARE v_name VARCHAR(255);
  DECLARE done INT DEFAULT 0;
  DECLARE cur CURSOR FOR SELECT id, name FROM products WHERE slug IS NULL;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_id, v_name;
    IF done THEN LEAVE read_loop; END IF;
    UPDATE products
       SET slug = LOWER(REGEXP_REPLACE(v_name, '[^a-zA-Z0-9]+', '-'))
     WHERE id = v_id;
  END LOOP;
  CLOSE cur;
END//
DELIMITER ;

CALL backfill_slugs();

-- 3) Error handling: trap a specific SQLSTATE and respond gracefully
DELIMITER //
CREATE PROCEDURE add_unique_customer(IN p_email VARCHAR(255))
BEGIN
  DECLARE CONTINUE HANDLER FOR 1062  -- 1062 = duplicate key
  BEGIN
    SELECT id FROM customers WHERE email = p_email;
  END;
  INSERT INTO customers (email, created_at) VALUES (p_email, NOW());
  SELECT LAST_INSERT_ID() AS id;
END//
DELIMITER ;

-- 4) Permissions — grant EXECUTE only, no direct DML
GRANT EXECUTE ON PROCEDURE shop.place_order TO 'app'@'%';
REVOKE INSERT, UPDATE, DELETE ON shop.orders FROM 'app'@'%';
FLUSH PRIVILEGES;

-- 5) Inspect and maintain
SHOW PROCEDURE STATUS WHERE Db = 'shop';
SHOW CREATE PROCEDURE place_order;
DROP PROCEDURE IF EXISTS place_order;

Why it matters

Source procedures into your repo as .sql files and apply them via migrations — never let "the database" be the source of truth. The moment a stored proc lives only in production, you have a config file that no code review touches and no diff tool tracks, which is how prod and staging quietly diverge.

Tip: Tweak the snippet with Try it Yourself », then sit the quiz at the bottom of the page.

Example

Example
DELIMITER //
CREATE PROCEDURE archive_old_orders()
BEGIN
    INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < NOW() - INTERVAL 1 YEAR;
    DELETE FROM orders WHERE created_at < NOW() - INTERVAL 1 YEAR;
END//
DELIMITER ;
Try it Yourself »

Discussion

Loading…