A function takes inputs and returns a value (or a table), and you use it inside a query. A stored procedure is a saved block of statements you run with CALL. It can change data, control transactions, and run several steps.
Differences that show up in practice
-- Function: used inside SELECT
SELECT order_id, tax_amount(total) FROM orders;
-- Procedure: runs a job
CALL load_daily_sales('2025-03-01');The differences, in short.
- A function is usually expected to be free of side effects. It computes and returns. In many engines it cannot do INSERT or UPDATE, and it cannot control transactions.
- A procedure can do DML (INSERT, UPDATE, MERGE), DDL, loops, and error handling, and may return no value at all.
- Functions are called from SQL expressions. Procedures are called as their own statement.
Names differ across engines, so be careful. BigQuery, Snowflake, SQL Server and Postgres (since version 11) all support both. User-defined functions can be written in SQL or in a language like JavaScript or Python, depending on the engine.
Where each is used in data work
Scalar functions package a business rule, such as converting currency or cleaning a phone number, so every query uses the same logic. Procedures often run multi-step warehouse loads: truncate staging, merge into the target, write an audit row.
The design opinion interviewers like
Procedures can grow into hidden pipelines. They are hard to version, retry, monitor and test compared to an orchestrated job. If a step can fail halfway and needs retries, alerting, dependencies and backfills, put the steps in an orchestrator such as Airflow or in dbt models, and keep each SQL step small and idempotent. A procedure is fine for a self-contained unit that is easy to rerun. Do not move the whole workflow into one.
One more caution: scalar UDFs, especially ones in JavaScript or Python, can be much slower than built-in functions over large data, because they may run row by row outside the optimized engine.