A database routine stores reusable logic in the database. A function often fits a question such as “what value does this expression produce?” A procedure often fits an operation such as “perform this update workflow.” The distinction becomes useful when you consider the caller, returned values and side effects.
The details vary considerably between PostgreSQL, MySQL and SQL Server, and SQLite has no ordinary built-in stored-procedure language comparable to those server databases. This guide provides a complete PostgreSQL example and then explains where the broader comparison needs engine-specific checking. It extends the original procedures chapter without changing its foundation.
A function has a return contract
A scalar function returns a single value for a call. Some engines also support functions that return sets or tables. A query can use eligible functions in expressions or as row sources, depending on the function's type and the engine's rules.
The return type is part of the contract. A percentage calculation should state whether it accepts integers or decimals and how it handles NULL and zero inputs. A table-returning function should state the columns and row meaning. The word “function” does not remove the need to define edge cases.
Side-effect restrictions differ. SQL Server user-defined functions have significant restrictions on modifying database state. PostgreSQL permits more kinds of function behavior and classifies volatility. MySQL has its own stored-function restrictions. Therefore, “functions can never modify data” is not a portable description of all SQL engines.
Avoid placing expensive or surprising work inside a function called once per output row. A readable expression can hide many repeated operations. Inspect the execution plan and workload if a reporting query invokes a database routine across a large result.
Procedures describe an operation
A procedure is invoked through the engine's procedure-call mechanism, such as CALL in PostgreSQL and MySQL or EXEC in SQL Server. Procedures can use parameters, return information through engine-specific mechanisms and perform workflows allowed by the engine.
For a PostgreSQL example, create a stock table and a simple procedure that increases one item's stock. This is an illustrative operation; a production inventory model also needs validation and authorization.
CREATE TABLE routine_stock (
product_id INTEGER PRIMARY KEY,
quantity INTEGER NOT NULL CHECK (quantity >= 0)
);
INSERT INTO routine_stock VALUES (1, 3);
CREATE PROCEDURE add_stock(p_product_id integer, p_quantity integer)
LANGUAGE sql
AS $$
UPDATE routine_stock
SET quantity = quantity + p_quantity
WHERE product_id = p_product_id;
$$;
CALL add_stock(1, 2);
SELECT quantity FROM routine_stock WHERE product_id = 1;The resulting quantity is 5. Decide explicitly what should happen for an unknown product ID or a negative requested increase. Silently changing zero rows is different from confirming that an inventory operation succeeded.
Transactions, permissions and portability
Do not assume every procedure controls its own transaction. PostgreSQL transaction-control permissions depend on how a procedure is invoked and other conditions. MySQL and SQL Server have different rules. A routine called inside an application's transaction may need to participate in that transaction rather than commit independently.
Security context is equally important. Some routines run with the caller's privileges; other configurations use the owner's or definer's context. Elevated routines should expose a narrow operation and validate inputs. In PostgreSQL security-definer functions, carefully configure object resolution and the search path to avoid unintended object substitution.
Parameterize dynamic SQL where supported and allowlist identifiers that must be assembled dynamically. A stored routine that concatenates user input into SQL can still be vulnerable to injection. Moving the unsafe string building into the database does not make it safe.
The PostgreSQL CREATE FUNCTION reference and CREATE PROCEDURE reference describe the example language and invocation rules. Consult the engine-specific manual before migrating either definition.
Choose from the caller's needs
If a SELECT needs a reusable, well-defined calculation, a suitable function may be a natural choice. If an application needs one authorized database operation that changes several related records, a procedure may make that boundary explicit. Ordinary parameterized SQL in the application can also be the clearest solution.
Consider maintenance. Stored logic needs version-controlled migration files, tests, deployment ordering and compatible callers. A routine name alone does not reveal whether its behavior changed. Treat parameter names, types, result columns and error conditions as a public contract for the application.
Avoid claiming that routines are always faster than application SQL. They can reduce round trips or centralize work, but their execution still depends on queries, indexes and engine optimization. Measure the relevant workload and include the cost of repeated calls.
Practice: define and test edge cases
Extend the discount calculation's specification before changing its code. Should negative gross prices be rejected? Should discounts above 100 be allowed? Should NULL mean unknown, no discount or an error? Write expected outcomes for those cases, then implement the chosen policy using PostgreSQL's appropriate validation mechanism or the caller's validated contract.
For the stock operation, add tests for a valid increase, a missing product and a request that would violate the nonnegative constraint. Check the resulting table, affected-row behavior and transaction outcome. A useful routine is understandable through those tests and its documented contract, not just through a successful example.
Finally, explain how a rollback in the caller should affect the stock change. Verify that behavior in the target engine rather than relying on a generic comparison chart about procedures and functions.
SQL Stored Procedures vs Functions Examples
Each example shows the code, the output it produces and what each part does.
Define a small PostgreSQL function
This code is specifically PostgreSQL SQL-language function syntax. Use a test database and a role allowed to create functions. The calculation uses an exact numeric return type and states its arithmetic directly.
The function depends only on its arguments, so IMMUTABLE is suitable for this calculation. It does not read a changing price table or the current time. Do not mark a data-dependent routine immutable merely to encourage optimization: the declaration must describe the actual behavior.
CREATE FUNCTION net_price(gross numeric, discount_percent numeric)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
AS $$
SELECT gross * (1 - discount_percent / 100);
$$;
SELECT net_price(200, 10) AS result;result
180SQL Stored Procedures vs Functions FAQ
Can a procedure return information?
Yes, through mechanisms such as output parameters, returned result sets or, in PostgreSQL, INOUT parameters, depending on the engine. A function is the better fit when the result should be used inside a query, such as in a SELECT list.
Can SQLite run this CREATE PROCEDURE code?
No. SQLite has no stored procedures or SQL-defined functions. SQLite applications commonly register host-language functions or implement workflows in application code, since the database runs inside the same program.
Are routines automatically injection-safe?
No. A routine that builds SQL text from its parameters and runs it, for example with EXECUTE in PL/pgSQL, is as vulnerable as application code. Safe parameter handling and controlled dynamic SQL, such as format() with %I and %L in PostgreSQL, are still necessary.
Should business logic live in stored procedures?
It's a trade-off. Logic in the database runs close to the data and is shared by every application, but it's harder to version, test and debug with ordinary tools. Many teams keep core rules in application code and use routines for data-heavy operations or strict integrity checks.
Which languages can PostgreSQL functions be written in?
SQL and PL/pgSQL are built in, and extensions add others, such as PL/Python and PL/Perl. Plain SQL functions are simplest and can be inlined by the planner; PL/pgSQL adds variables, loops and exception handling.
SQL Stored Procedures vs Functions Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
A PostgreSQL discount function reads a changing price table. Is IMMUTABLE an appropriate declaration?
SQL Stored Procedures vs Functions Quiz
2 questions to check what you learned.
Which PostgreSQL command invokes a procedure?
- CALL
- ORDER BY
- PARTITION BY
Which comparison is safe across SQL engines?
- Functions can never change data in any engine
- Check each engine's restrictions before relying on function side effects or procedure transaction control
- Procedures always commit independently of their callers