Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

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.

~5 min readSQL
#SQL#Database Functions#Stored Procedures
3D illustration of a procedural batch execution workflow contrasted with deterministic scalar and table-valued function pipes

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

sql
-- 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');
sql
-- 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
-- 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

PropertyStored procedureFunction
InvocationCALL p() / EXEC pSELECT f() or FROM f()
Return valueNone or OUT/INOUT parametersRequired, scalar or set
Usable inside a queryNoYes
Usable in an expression indexNoYes, if immutable
Transaction control (COMMIT, ROLLBACK)Yes in PostgreSQL and MySQLNo
Can modify tablesYesPostgreSQL yes (not IMMUTABLE); SQL Server no
Multiple result setsYesNo
Overloading by argument typesPostgreSQL yesPostgreSQL yes
Parameter modeIN, OUT, INOUTIN only
Plan cachingPer session, parameterisedPer query, inlined or cached
Error handlingEXCEPTION block / handlerEXCEPTION block / handler
Typical useBatch jobs, migrations, orchestrationReusable logic, computed columns, predicates
EngineProcedure objectFunction volatility / determinismNotes
PostgreSQLCREATE PROCEDURE, CALLIMMUTABLE, STABLE, VOLATILEProcedures added in 11
MySQLCREATE PROCEDUREDETERMINISTIC, NO SQL, READS SQL DATABinlog requires determinism for replicated functions
SQL ServerCREATE PROCEDUREDeterministic or not, no declaration neededUDFs may not modify state
OracleCREATE PROCEDURE / FUNCTION in a packageDETERMINISTIC, PARALLEL_ENABLEBoth in PL/SQL packages
SQLiteNot supportedCREATE FUNCTION via extensionHost-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:

code
ERROR: invalid transaction termination CONTEXT: PL/pgSQL function close_month(date) line 8 at COMMIT

That 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:

sql
-- 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

code
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:

sql
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:

sql
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

sql
-- 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:

sql
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 symptomCauseFix
invalid transaction terminationProcedure commits inside an open transaction blockCall at top level, or remove the COMMIT
function ... does not exist on a known functionArgument types do not match, or schema not in search_pathCast the arguments explicitly; qualify the schema
function is not uniqueOverloads with implicit castsCast arguments so one overload wins
Expression index never usedFunction marked IMMUTABLE but timezone or clock dependentUse STABLE and a fixed AT TIME ZONE
Msg 443: Invalid use of a side-effecting operatorSQL Server function writes dataMove the write to a procedure
Msg 2772: Cannot access temporary tables from within a functionTemp table inside a UDFUse a procedure, or a table variable
Procedure suddenly slow after a data changeParameter sniffing / generic planOPTION (RECOMPILE) or a custom plan
RETURNS TABLE function returns nothingDeclared STRICT or returned a non-matching typeCheck RETURNS types and remove STRICT
permission denied for function after adding SECURITY DEFINEREXECUTE not granted, or search_path hijack blockedGRANT EXECUTE, then set search_path
MySQL DELIMITER error in a migrationDelimiter not reset after the objectReset 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 tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.