SQL Stored Procedures vs Functions: Syntax, Parameters, and Execution Differences
Procedures act, functions compute: transaction control, IN/OUT parameters, volatility that lies, and the plan caching that makes procedures slow.

Quick Summary
A stored procedure is a named block of statements you invoke with CALL. It can read and write tables, manage transactions, return zero or many result sets through output parameters, and it never appears inside a SELECT. A function returns a value — scalar or set-returning — and by design it can be composed into a query: SELECT * FROM my_function(42), WHERE lower_safe(email) = $1, or an expression index.
That usability gap is the whole design decision. Functions are for computation you want the optimizer to see and reuse; procedures are for operations that orchestrate several steps, and where transaction control or multiple result sets are part of the job. Engines enforce the split with different strictness: PostgreSQL lets a function write to tables as long as it is not IMMUTABLE, SQL Server forbids side effects inside a function outright, and MySQL separates the two into different objects with different privileges.
Most production incidents here come from treating one as the other: a procedure wrapped in a transaction that it then tries to commit, a function marked IMMUTABLE that reads the clock, or a set-returning function placed in the WHERE clause of a query over a million rows.
Basic syntax and query signatures
-- PostgreSQL: function
CREATE OR REPLACE FUNCTION order_total(p_order_id bigint)
RETURNS numeric
LANGUAGE sql
STABLE
AS $$
SELECT COALESCE(SUM(unit_price * quantity), 0)
FROM order_items
WHERE order_id = p_order_id
$$;
SELECT order_total(8812);
-- PostgreSQL: set-returning function, used as a table
CREATE OR REPLACE FUNCTION recent_orders(p_days int)
RETURNS TABLE (id bigint, placed_at timestamptz, total numeric)
LANGUAGE sql STABLE AS $$
SELECT o.id, o.placed_at, order_total(o.id)
FROM orders o
WHERE o.placed_at > now() - make_interval(days => p_days)
$$;
SELECT * FROM recent_orders(7) WHERE total > 100;
-- PostgreSQL: procedure with transaction control
CREATE OR REPLACE PROCEDURE close_month(p_month date)
LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO monthly_totals (month, revenue)
SELECT date_trunc('month', p_month), SUM(amount_cents) FROM payments
WHERE paid_at >= p_month AND paid_at < p_month + interval '1 month'
GROUP BY 1
ON CONFLICT (month) DO UPDATE SET revenue = EXCLUDED.revenue;
COMMIT;
UPDATE reporting_state SET last_closed = p_month;
END $$;
CALL close_month('2026-10-01');-- MySQL: procedure and function are separate objects
DELIMITER $$
CREATE PROCEDURE add_credit(IN p_user BIGINT, IN p_cents INT, OUT p_balance INT)
BEGIN
UPDATE accounts SET balance_cents = balance_cents + p_cents WHERE user_id = p_user;
SELECT balance_cents INTO p_balance FROM accounts WHERE user_id = p_user;
END $$
CREATE FUNCTION order_total(p_order BIGINT) RETURNS DECIMAL(12,2)
DETERMINISTIC READS SQL DATA
BEGIN
DECLARE v DECIMAL(12,2);
SELECT COALESCE(SUM(unit_price * quantity), 0) INTO v FROM order_items WHERE order_id = p_order;
RETURN v;
END $$
DELIMITER ;
CALL add_credit(7, 500, @bal);
SELECT order_total(8812), @bal;-- SQL Server: procedure versus inline table-valued function
CREATE PROCEDURE dbo.RecentOrders @Days int
AS
BEGIN
SET NOCOUNT ON;
SELECT o.id, o.placed_at, o.total
FROM dbo.Orders o
WHERE o.placed_at > DATEADD(day, -@Days, SYSUTCDATETIME());
END;
CREATE FUNCTION dbo.RecentOrdersFn (@Days int)
RETURNS TABLE
AS RETURN
SELECT o.id, o.placed_at, o.total
FROM dbo.Orders o
WHERE o.placed_at > DATEADD(day, -@Days, SYSUTCDATETIME());Key comparison table
| Property | Stored procedure | Function |
|---|---|---|
| Invocation | CALL p() / EXEC p | SELECT f() or FROM f() |
| Return value | None or OUT/INOUT parameters | Required, scalar or set |
| Usable inside a query | No | Yes |
| Usable in an expression index | No | Yes, if immutable |
Transaction control (COMMIT, ROLLBACK) | Yes in PostgreSQL and MySQL | No |
| Can modify tables | Yes | PostgreSQL yes (not IMMUTABLE); SQL Server no |
| Multiple result sets | Yes | No |
| Overloading by argument types | PostgreSQL yes | PostgreSQL yes |
| Parameter mode | IN, OUT, INOUT | IN only |
| Plan caching | Per session, parameterised | Per query, inlined or cached |
| Error handling | EXCEPTION block / handler | EXCEPTION block / handler |
| Typical use | Batch jobs, migrations, orchestration | Reusable logic, computed columns, predicates |
| Engine | Procedure object | Function volatility / determinism | Notes |
|---|---|---|---|
| PostgreSQL | CREATE PROCEDURE, CALL | IMMUTABLE, STABLE, VOLATILE | Procedures added in 11 |
| MySQL | CREATE PROCEDURE | DETERMINISTIC, NO SQL, READS SQL DATA | Binlog requires determinism for replicated functions |
| SQL Server | CREATE PROCEDURE | Deterministic or not, no declaration needed | UDFs may not modify state |
| Oracle | CREATE PROCEDURE / FUNCTION in a package | DETERMINISTIC, PARALLEL_ENABLE | Both in PL/SQL packages |
| SQLite | Not supported | CREATE FUNCTION via extension | Host-language functions |
Practical edge cases and deep dives
1. A procedure that commits inside an open transaction
PostgreSQL procedures may run COMMIT and ROLLBACK, but only when invoked outside a transaction block. In a client that wraps every statement in a transaction — most ORMs and migration tools do — the call fails:
ERROR: invalid transaction termination
CONTEXT: PL/pgSQL function close_month(date) line 8 at COMMITThat error means the procedure wanted to end a transaction it did not start. Three fixes: call it with autocommit at the top level, remove the COMMIT and let the caller decide, or split it into a function that does the work plus a job that commits. The third is usually correct for a scheduled job, since a procedure that commits partway through cannot be rolled back as a unit — half of it will have happened even if the second half fails.
2. Volatility that lies
STABLE promises the function returns the same result for the same arguments within one statement; IMMUTABLE promises it always will, forever. The planner believes both. Mark a now()-reading function IMMUTABLE and use it in an expression index, and the index keeps the values from whenever the rows were written while queries compute a fresh value — the index is never used, and you get a full scan with no error:
-- wrong: depends on the clock, declared immutable
CREATE FUNCTION day_key(ts timestamptz) RETURNS date
LANGUAGE sql IMMUTABLE AS $$ SELECT ts::date $$;
CREATE INDEX orders_day_idx ON orders (day_key(placed_at)); -- silently useless
-- right
CREATE OR REPLACE FUNCTION day_key(ts timestamptz) RETURNS date
LANGUAGE sql STABLE AS $$ SELECT (ts AT TIME ZONE 'UTC')::date $$;Timezone-dependent casts are the other classic: timestamptz::date depends on TimeZone, so it is STABLE, not IMMUTABLE. The same rule appears in MySQL as DETERMINISTIC, where a non-deterministic function used in a replicated statement breaks replicas.
3. SQL Server: functions cannot do that
Msg 443: Invalid use of a side-effecting operator 'INSERT' within a function.
Msg 2772: Cannot access temporary tables from within a function.SQL Server user-defined functions are pure by contract: no INSERT, UPDATE, DELETE, no temp tables, no TRY...CATCH, no dynamic SQL. Anything that needs those becomes a procedure — which then cannot be joined to a query. The practical pattern is an inline table-valued function for read logic (RETURNS TABLE AS RETURN SELECT ...), which the optimizer inlines like a view, and a procedure for write logic. Avoid multi-statement table-valued functions in hot paths: they are estimated at a fixed row count, and the resulting plan is frequently a nested loop over a large scan.
4. Parameter sniffing and plan reuse
SQL Server compiles a procedure's plan on first execution using the parameters it saw. A report that ran once with a customer holding three orders then runs for a customer holding three million:
CREATE PROCEDURE dbo.OrdersFor @CustomerId int AS
SELECT * FROM dbo.Orders WHERE customer_id = @CustomerId;
-- first run: 3 rows -> nested loops + seek, cached
-- later run: 3,000,000 rows -> same seek plan, 40 seconds
-- remedies
CREATE PROCEDURE dbo.OrdersFor @CustomerId int
AS
SELECT * FROM dbo.Orders WHERE customer_id = @CustomerId
OPTION (RECOMPILE); -- or: OPTION (OPTIMIZE FOR (@CustomerId = 1))PostgreSQL has the same shape with prepared statements: it uses a custom plan for the first five executions and then may switch to a generic plan, which can be dramatically worse for skewed data. plan_cache_mode = force_custom_plan on the session resolves it.
5. SECURITY DEFINER without a fixed search_path
A function that runs with the owner's privileges and resolves unqualified names through the caller's search_path is a privilege escalation path: the caller creates a table named orders in a schema that comes first, and the definer-privileged function writes to it. PostgreSQL's own documentation calls this out, and the fix is one line:
CREATE FUNCTION purge_old_events() RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$ ... $$;The same applies to SECURITY DEFINER procedures and to Oracle definer's-rights packages. If the object does not need elevated rights, invoker's rights — the default — is safer.
6. Calling a set-returning function per row
-- looks innocent, runs the function 1,000,000 times
SELECT o.id, order_total(o.id) FROM orders o;A scalar subquery or function call in the target list is evaluated once per row unless the planner can flatten it. If the function does its own SELECT, you have created an N+1 pattern inside the database. Rewrite it as a lateral join with an aggregate, and check the plan for a Function Scan with a high loop count:
SELECT o.id, t.total
FROM orders o
LEFT JOIN LATERAL (
SELECT SUM(unit_price * quantity) AS total FROM order_items i WHERE i.order_id = o.id
) t ON true;Common mistakes and runtime errors
| Error or symptom | Cause | Fix |
|---|---|---|
invalid transaction termination | Procedure commits inside an open transaction block | Call at top level, or remove the COMMIT |
function ... does not exist on a known function | Argument types do not match, or schema not in search_path | Cast the arguments explicitly; qualify the schema |
function is not unique | Overloads with implicit casts | Cast arguments so one overload wins |
| Expression index never used | Function marked IMMUTABLE but timezone or clock dependent | Use STABLE and a fixed AT TIME ZONE |
Msg 443: Invalid use of a side-effecting operator | SQL Server function writes data | Move the write to a procedure |
Msg 2772: Cannot access temporary tables from within a function | Temp table inside a UDF | Use a procedure, or a table variable |
| Procedure suddenly slow after a data change | Parameter sniffing / generic plan | OPTION (RECOMPILE) or a custom plan |
RETURNS TABLE function returns nothing | Declared STRICT or returned a non-matching type | Check RETURNS types and remove STRICT |
permission denied for function after adding SECURITY DEFINER | EXECUTE not granted, or search_path hijack blocked | GRANT EXECUTE, then set search_path |
MySQL DELIMITER error in a migration | Delimiter not reset after the object | Reset DELIMITER ; at the end of the script |
One rule keeps this area clean: functions compute, procedures act. When a function needs to write, ask whether the caller really needs it inside a query — usually the answer is that the query should join to a table the write already populated, and the function should not exist.
Try matching browser tools
All toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesSQL Cursors vs Set-Based Operations: Syntax, Memory, and Performance
Why a row-by-row cursor costs minutes where one UPDATE takes seconds, how server cursors spool to disk, and the set-based replacements for each loop.
SQL String Functions: CONCAT, SUBSTRING, REPLACE, and Pattern Matching
The concatenation and slice functions that differ between engines, why LIKE 'x%' uses an index and '%x%' cannot, plus escaping and NULL rules.
SQL Date and Time Functions: INTERVAL Arithmetic, Time Zones, and DST
timestamptz versus timestamp, half-open ranges that keep an index, and the daylight saving gaps that make local scheduling wrong once a year.
SQL Row Locking: SELECT FOR UPDATE, SKIP LOCKED, and NOWAIT
How FOR UPDATE locks rows until commit, why the plan decides the lock scope, and the SKIP LOCKED claim-queue pattern that scales to many workers.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.