What is the difference between a stored procedure and a user-defined function?
Quick Answer
A function must return a value (scalar or a table), can't have side effects (no INSERT/UPDATE/DELETE on base tables), and is used inside a query — SELECT dbo.fn_Tax(price). A stored procedure can modify data, run transactions, return multiple result sets, and is called with EXEC, not embedded.
A function has to return something — a single value or a table — it can't change data in the base tables, and you use it inside a query. A stored procedure is called with EXEC, it can insert, update and delete, manage its own transaction and error handling, and return multiple result sets. The performance point I'd add is that a scalar function called in a SELECT used to run once per row and kill parallelism — that's better since SQL Server 2019 inlines them, but I still prefer an inline table-valued function where I can.
| Function | Stored procedure | |
|---|---|---|
| Returns | Scalar or table (required) | Optional; result sets + output params |
| Data modification | No (except table variables) | Yes |
| Use in a query | Yes | No |
| Try/catch, transactions | Limited | Full |
| Called with | Inline in SQL | EXEC |
Performance trap: a scalar UDF in a SELECT or WHERE historically ran once per row and blocked parallelism (improved by inlining in SQL Server 2019+). Prefer inline table-valued functions.