MySQL Stored Procedures vs Functions: When to Use Which

Stored procedures and stored functions are both named SQL routines that live in the database, but they serve different purposes. Procedures perform actions and return multiple result sets. Functions return a single value and can be used inside SQL statements. Use procedures for complex operations, functions for computed values.

MySQL has supported stored procedures since version 5.0. They are powerful but often misunderstood. The difference between a procedure and a function is not just syntactic, it changes how you use them.

The naming is confusing because most programming languages use the word "function" for any callable routine. In SQL, a function must return a value and can be used inside other SQL statements. A procedure performs actions and is called separately. This distinction matters when you are deciding where to put logic in your database layer.

One practical difference that trips people up is transaction control. Procedures can start, commit, and roll back transactions. Functions cannot. If you need to wrap multiple operations in a transaction, you must use a procedure. This is a fundamental architectural constraint, not a minor detail, and it affects how you structure your database logic from the start.

A common performance issue: using a function where a procedure was needed. When a function is called for every row in a 10-million-row table, it causes significant overhead. A simple procedure would handle the batch operation in seconds.

Stored Procedures: Perform Actions

A stored procedure is a named collection of SQL statements that perform an action. It can take parameters, execute multiple queries, and return result sets.

Procedures are the workhorses of database-side logic. When you need to perform multiple operations in sequence (update a record, create related records, log an event), a procedure wraps them in a single call. This reduces network round-trips and keeps related logic together. The downside is that procedures are harder to version control and test compared to application code, so use them where they genuinely simplify your architecture.

-- Create a procedure
DELIMITER //
CREATE PROCEDURE GetUserOrders(IN p_user_id INT)
BEGIN
  SELECT * FROM orders WHERE user_id = p_user_id;
  SELECT COUNT(*) as order_count FROM orders WHERE user_id = p_user_id;
END //
DELIMITER ;

-- Call the procedure
CALL GetUserOrders(123);

Procedures can:

• Take input parameters (IN), output parameters (OUT), or both (INOUT)
• Return multiple result sets
• Use variables, loops, and conditionals
• Commit or rollback transactions
• Call other procedures

Stored Functions: Return Values

A stored function is a named routine that returns a single value. It can be used anywhere an expression is valid in a SQL statement.

-- Create a function
DELIMITER //
CREATE FUNCTION CalculateDiscount(p_total DECIMAL(10,2), p_percentage DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  RETURN p_total * (1 - p_percentage / 100);
END //
DELIMITER ;

-- Use the function in a query
SELECT id, total, CalculateDiscount(total, 10) as discounted_price
FROM orders;

Functions can:

• Take input parameters (but not OUT or INOUT)
• Return a single value using the RETURN statement
• Be used in SELECT, WHERE, and other SQL expressions
• Call other functions

Key Differences

When to Use Procedures

Use procedures when you need to:

• Perform multiple operations as a single unit
• Return multiple result sets
• Use output parameters to return multiple values
• Manage transactions explicitly
• Implement complex business logic that involves multiple tables

-- Example: Process an order
DELIMITER //
CREATE PROCEDURE ProcessOrder(
  IN p_order_id INT,
  OUT p_status VARCHAR(20)
)
BEGIN
  DECLARE v_total DECIMAL(10,2);

  -- Check if order exists
  SELECT total INTO v_total FROM orders WHERE id = p_order_id;

  IF v_total IS NULL THEN
    SET p_status = 'NOT_FOUND';
  ELSE
    -- Update order status
    UPDATE orders SET status = 'processing' WHERE id = p_order_id;

    -- Create shipment record
    INSERT INTO shipments (order_id, status) VALUES (p_order_id, 'pending');

    SET p_status = 'SUCCESS';
  END IF;
END //
DELIMITER ;

-- Call the procedure
CALL ProcessOrder(123, @status);
SELECT @status;

When to Use Functions

Use functions when you need to:

• Compute a value from input parameters
• Use the result in a SELECT, WHERE, or ORDER BY clause
• Create reusable calculations
• Encapsulate complex expressions

-- Example: Format a price with currency
DELIMITER //
CREATE FUNCTION FormatPrice(p_amount DECIMAL(10,2), p_currency VARCHAR(3))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
  RETURN CONCAT(p_currency, ' ', FORMAT(p_amount, 2));
END //
DELIMITER ;

-- Use in queries
SELECT id, FormatPrice(total, 'USD') as formatted_total
FROM orders;

-- Use in WHERE clause to filter by discount threshold
SELECT * FROM orders
WHERE total > 100 AND CalculateDiscount(total, 5) > 10;

The DETERMINISTIC Keyword

Functions must be declared as DETERMINISTIC or NOT DETERMINISTIC. A deterministic function always returns the same result for the same inputs. A non-deterministic function might return different results for the same inputs (like NOW() or RAND()).

-- Deterministic: Same inputs always produce same output
CREATE FUNCTION Add(a INT, b INT) RETURNS INT DETERMINISTIC
BEGIN
  RETURN a + b;
END;

-- Non-deterministic: Same inputs might produce different output
CREATE FUNCTION RandomBetween(min_val INT, max_val INT) RETURNS INT NOT DETERMINISTIC
BEGIN
  RETURN FLOOR(min_val + RAND() * (max_val - min_val + 1));
END;

Performance Considerations

Stored procedures and functions have overhead:

• Parsing - Routines are parsed at CREATE time; subsequent calls use the cached execution plan
• Network - Procedures reduce network round-trips for complex operations
• Security - Users can execute procedures without direct table access

For simple operations, inline SQL is often faster than calling a routine. For complex operations involving multiple statements, procedures are faster because they reduce network round-trips.

The performance difference matters most over high-latency connections. If your application connects to MySQL over a network with 10ms latency, each round-trip costs 10ms. A procedure that runs five queries in one call saves 40ms compared to running them from the application. On a local connection, the difference is negligible, so the decision should be based on code organization rather than performance.

Common Mistakes

1. Using functions for multi-statement operations - Functions should return a single value, not perform complex actions
2. Forgetting DETERMINISTIC - MySQL requires this declaration for functions
3. Not handling errors - Procedures should include error handling with DECLARE HANDLER
4. Overusing stored procedures - Business logic is often better in the application layer

Key Takeaways

• Procedures perform actions and return result sets; functions return single values and work in SQL expressions
• Use procedures for complex operations involving multiple statements; use functions for computed values in SELECT queries
• Functions must be declared DETERMINISTIC or NOT DETERMINISTIC; procedures do not have this requirement
• Neither is a replacement for proper application architecture; use them where they genuinely simplify your code
• Test performance of routines versus inline SQL; simple operations are often faster without the routine overhead

FAQ

Can a function return multiple values?

No. A function returns a single value. If you need to return multiple values, use a procedure with OUT parameters or a procedure that returns result sets.

Can a procedure be used in a SELECT statement?

No. Procedures must be called with the CALL statement. If you need to use a routine in SELECT, create a function instead.

Should I put business logic in stored procedures?

It depends. For database-centric applications (reporting, data processing), stored procedures make sense. For web applications with complex business logic, the application layer is usually better because it is easier to test, version control, and deploy.

Can I call a function from a procedure?

Yes. Procedures can call functions within their SQL statements. For example, a procedure might use a function to calculate a value before inserting it into a table. This is a common pattern for separating calculation logic from action logic.

What happens if I delete a routine that other routines depend on?

MySQL allows you to drop a routine even if other routines call it. The dependent routines will fail when executed. Always check for dependencies before dropping routines in production environments.

Real-World Example: Order Processing System

An online store needs to process orders, apply discounts, and generate invoices. Here is how procedures and functions work together in a typical scenario:

-- Function to calculate tax based on state
DELIMITER //
CREATE FUNCTION CalculateTax(p_amount DECIMAL(10,2), p_state VARCHAR(2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  DECLARE v_tax_rate DECIMAL(5,4);
  
  SELECT tax_rate INTO v_tax_rate 
  FROM state_taxes 
  WHERE state_code = p_state;
  
  RETURN p_amount * COALESCE(v_tax_rate, 0);
END //
DELIMITER ;

-- Procedure to process a complete order
DELIMITER //
CREATE PROCEDURE ProcessNewOrder(
  IN p_customer_id INT,
  IN p_product_id INT,
  IN p_quantity INT,
  OUT p_invoice_total DECIMAL(10,2)
)
BEGIN
  DECLARE v_subtotal DECIMAL(10,2);
  DECLARE v_discount DECIMAL(10,2);
  DECLARE v_tax DECIMAL(10,2);
  DECLARE v_state VARCHAR(2);
  
  -- Get customer state for tax calculation
  SELECT state INTO v_state FROM customers WHERE id = p_customer_id;
  
  -- Calculate subtotal
  SELECT price * p_quantity INTO v_subtotal 
  FROM products WHERE id = p_product_id;
  
  -- Apply discount if customer qualifies
  IF v_subtotal > 100 THEN
    SET v_discount = v_subtotal * 0.10;
  ELSE
    SET v_discount = 0;
  END IF;
  
  -- Calculate tax using the function
  SET v_tax = CalculateTax(v_subtotal - v_discount, v_state);
  
  -- Set final total
  SET p_invoice_total = v_subtotal - v_discount + v_tax;
  
  -- Insert order record
  INSERT INTO orders (customer_id, product_id, quantity, subtotal, discount, tax, total)
  VALUES (p_customer_id, p_product_id, p_quantity, v_subtotal, v_discount, v_tax, p_invoice_total);
END //
DELIMITER ;

-- Call the procedure
CALL ProcessNewOrder(42, 101, 3, @total);
SELECT @total as invoice_total;

The function handles a pure calculation (tax rate lookup and multiplication). The procedure orchestrates the entire workflow (get data, calculate, insert records). This separation makes each piece easier to test and maintain.

Error Handling in Procedures

Production procedures need proper error handling. Without it, a failed query in the middle of a procedure leaves data in an inconsistent state:

DELIMITER //
CREATE PROCEDURE TransferFunds(
  IN p_from_account INT,
  IN p_to_account INT,
  IN p_amount DECIMAL(10,2)
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;
  
  START TRANSACTION;
  
  -- Deduct from source
  UPDATE accounts SET balance = balance - p_amount 
  WHERE id = p_from_account AND balance >= p_amount;
  
  IF ROW_COUNT() = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
  END IF;
  
  -- Add to destination
  UPDATE accounts SET balance = balance + p_amount 
  WHERE id = p_to_account;
  
  COMMIT;
END //
DELIMITER ;

The DECLARE HANDLER catches any SQL exception, rolls back the transaction, and re-raises the error. This ensures the transfer either completes fully or not at all, preventing partial updates that would corrupt account balances.

Error handling in procedures is not optional in production code. Without it, a failed query in the middle of a procedure leaves data in a partially updated state. The DECLARE HANDLER syntax is MySQL-specific, so if you ever migrate to another database, you will need to rewrite the error handling logic. Despite this, the protection it provides during normal operation is worth the coupling. Always declare handlers for SQLEXCEPTION in procedures that modify multiple tables.