Writing Stored Procedures and Functions

Some database engines let you encapsulate logic inside the database itself. Stored procedures and functions can place orders, calculate customer value, or enforce business rules close to the data. MySQL supports stored routines, and the examples below use MySQL syntax.

Learning Goals

See how stored routines can manage marketplace data
Understand the difference between procedures and functions
Handle parameters, return values, and error conditions
Decide when database-side code makes sense

Stored Procedure Example

Operations wants a single call to place a simple order and log the action.

MySQL stored procedure
DELIMITER $$

CREATE PROCEDURE place_order(
  IN p_customer_id INT,
  IN p_product_id  INT,
  IN p_quantity    INT
)
BEGIN
  DECLARE v_unit_price DECIMAL(10,2);
  DECLARE v_order_id INT;

  SELECT price
  INTO v_unit_price
  FROM product
  WHERE product_id = p_product_id;

  IF v_unit_price IS NULL THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = CONCAT('Product ', p_product_id, ' does not exist');
  END IF;

  INSERT INTO orders (customer_id, order_date, status, order_total)
  VALUES (p_customer_id, CURRENT_DATE(), 'Paid', v_unit_price * p_quantity);

  SET v_order_id = LAST_INSERT_ID();

  INSERT INTO order_item (order_id, product_id, quantity, unit_price, discount)
  VALUES (v_order_id, p_product_id, p_quantity, v_unit_price, 0);

  INSERT INTO audit_log (action, details, created_at)
  VALUES (
    'place_order',
    CONCAT('customer ', p_customer_id, ' -> product ', p_product_id, ' (qty ', p_quantity, ')'),
    NOW()
  );
END$$

DELIMITER ;

Execute the procedure:

CALL place_order(3, 7, 2);

If any statement inside the procedure fails, MySQL rolls back the current statement. Wrap multi-step routines in transactions if you need full atomicity.

User-Defined Function

A scalar function can compute helpful values such as a customer's lifetime value.

Calculate customer lifetime value
DELIMITER $$

CREATE FUNCTION customer_lifetime_value(
  p_customer_id INT
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  DECLARE v_total DECIMAL(10,2);

  SELECT COALESCE(SUM(t.amount), 0)
  INTO v_total
  FROM transactions AS t
  JOIN orders AS o ON o.order_id = t.order_id
  WHERE o.customer_id = p_customer_id
    AND t.status = 'Captured';

  RETURN v_total;
END$$

DELIMITER ;

Use it in queries:

SELECT customer_id,
       customer_lifetime_value(customer_id) AS lifetime_value
FROM customer
ORDER BY lifetime_value DESC;

Handling Errors and Transactions

Stored procedures often coordinate multi-step changes. Add explicit validation and error handling when needed.

Assign seller with safeguards
DELIMITER $$

CREATE PROCEDURE assign_seller(
  IN p_product_id INT,
  IN p_seller_id  INT
)
BEGIN
  DECLARE v_exists INT;

  SELECT COUNT(*) INTO v_exists
  FROM seller
  WHERE seller_id = p_seller_id;

  IF v_exists = 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = CONCAT('Seller ', p_seller_id, ' does not exist');
  END IF;

  UPDATE product
  SET seller_id = p_seller_id
  WHERE product_id = p_product_id;

  IF ROW_COUNT() = 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = CONCAT('Product ', p_product_id, ' does not exist');
  END IF;
END$$

DELIMITER ;

When to Use Stored Routines

Centralize shared logic such as order validation, inventory checks, or payment recording.
Reduce network chatter for multi-step operations that would otherwise require several round trips.
Enforce security by giving applications permission to execute a procedure without direct table access.

Limitations and cautions:

Stored procedures tie logic to a specific database engine; portability suffers.
Application code may be easier to version-control and test using standard tooling.
Overusing complex procedures can obscure data flow; balance database-side and application-side logic.

Summary

Stored procedures perform actions and are invoked with CALL.
Functions return values and can be used in SELECT, WHERE, or ORDER BY.
Use parameters to pass context; raise exceptions when validation fails.
Prefer database-side routines for shared, sensitive, or performance-critical operations.

Quiz

Show quiz

Which statement invokes the place_order procedure for customer 12?
A) SELECT place_order(12, 3, 1);
B) CALL place_order(12, 3, 1);
C) EXECUTE place_order;
D) RUN place_order(12, 3, 1);
What does customer_lifetime_value return when a customer has no captured transactions?
A) NULL
B) 0
C) 'N/A'
D) An error
Why might you raise an error inside a stored procedure?
A) To speed up execution
B) To abort the procedure when validation fails
C) To automatically log success
D) To refresh materialized views
True or False: Functions that make data modifications can be freely used inside SELECT statements in all databases.
A) True
B) False

Answers

Stored Procedure Example​

User-Defined Function​

Handling Errors and Transactions​

When to Use Stored Routines​

Summary​

Quiz​