SQL Stored Procedures vs Functions — Key Differences with Examples

Stored procedures and user-defined functions both let you save reusable SQL logic inside the database, but they are built for different jobs. Mixing them up leads to code that either can't be called where you need it, or can't do what you're asking it to do. This guide compares their syntax, capabilities, and ideal use cases with working MySQL examples and PostgreSQL notes.

Core Differences at a Glance

• Return value — a function must return exactly one value (or a table); a procedure can return zero, one, or multiple result sets, and can use OUT parameters instead
• Calling syntax — a procedure is invoked with CALL proc_name(); a function is called inline, like SELECT func_name(column) FROM table
• Use inside SELECT — functions can be used directly in a SELECT list, WHERE clause, or ORDER BY; procedures cannot
• Data modification — procedures freely run INSERT, UPDATE, DELETE, and manage transactions; in MySQL, functions cannot modify data if they are called from a SELECT statement, and in general are meant to be side-effect-free
• Parameters — procedures support IN, OUT, and INOUT parameters; functions only support IN parameters

Creating a Stored Procedure (MySQL)

A stored procedure that gives a raise to every employee in a department and reports how many rows were affected using an OUT parameter:

DELIMITER $$

CREATE PROCEDURE give_department_raise(
  IN dept_name VARCHAR(100),
  IN raise_percent DECIMAL(5,2),
  OUT rows_updated INT
)
BEGIN
  UPDATE employees
  SET salary = salary * (1 + raise_percent / 100)
  WHERE department = dept_name;

  SET rows_updated = ROW_COUNT();
END $$

DELIMITER ;

-- Call it:
CALL give_department_raise('Engineering', 5.0, @updated);
SELECT @updated AS rows_updated;

The procedure runs an UPDATE statement — something a MySQL function is not allowed to do when called from a SELECT context. It communicates the result back through the OUT parameter rather than a return value.

Creating a Function (MySQL)

A function that calculates a discounted price and can be dropped straight into a SELECT statement:

DELIMITER $$

CREATE FUNCTION calculate_discounted_price(
  original_price DECIMAL(10,2),
  discount_percent DECIMAL(5,2)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  RETURN original_price - (original_price * discount_percent / 100);
END $$

DELIMITER ;

-- Use it directly inside a query:
SELECT
  product_name,
  price,
  calculate_discounted_price(price, 15) AS sale_price
FROM products
WHERE category = 'Electronics';

Notice the function is used exactly like a built-in function such as ROUND() or UPPER() — inline, inside the column list. A stored procedure could never appear there.

PostgreSQL Differences

PostgreSQL historically only had functions, and used them for both roles (a function with no return value acted like a procedure). Since PostgreSQL 11, true CREATE PROCEDURE support was added, called with CALL, and procedures there can manage their own transactions with COMMIT/ROLLBACK — something functions still cannot do.

-- PostgreSQL function
CREATE FUNCTION calculate_discounted_price(original_price NUMERIC, discount_percent NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
  RETURN original_price - (original_price * discount_percent / 100);
END;
$$ LANGUAGE plpgsql;

-- PostgreSQL procedure (11+)
CREATE PROCEDURE give_department_raise(dept_name TEXT, raise_percent NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE employees
  SET salary = salary * (1 + raise_percent / 100)
  WHERE department = dept_name;
  COMMIT;
END;
$$;

CALL give_department_raise('Engineering', 5.0);

When to Use Which

1. Use a function when you need a small, reusable calculation embedded inside SELECT, WHERE, or ORDER BY — like formatting, discounting, or scoring a value per row.
2. Use a stored procedure when you need to run multiple statements as a unit, manage transactions explicitly, or perform batch INSERT/UPDATE/DELETE operations.
3. Use a procedure when the caller needs multiple outputs via OUT parameters, or multiple result sets returned at once.
4. Use a function when the logic needs to compose with other SQL, since it behaves like any other expression.

Common Pitfalls

• Trying to call a stored procedure from inside a SELECT — this is not allowed in MySQL or PostgreSQL; procedures are called as a standalone statement
• Forgetting DETERMINISTIC or NOT DETERMINISTIC in MySQL function definitions when binary logging is enabled with strict mode
• Overusing functions for heavy row-by-row logic in large SELECTs, which can hurt performance since the function executes once per row
• Assuming functions can freely write data in MySQL — they generally cannot when invoked from a SQL statement, unlike procedures

Frequently Asked Questions