SQL Reference ยท JSON and Special Features
SQL stored procedures and functions
Concept guide to stored procedures and user-defined functions: what they are, CREATE PROCEDURE in each database, and how to replace them in SQLite.
A stored procedure is a named block of SQL (usually with variables, conditions and loops) saved inside the database and run with a call. A stored function is similar but returns a value and can be used inside queries. Both run next to the data, can take parameters, and let you share logic between applications and enforce permissions: a user may be allowed to call a procedure without being allowed to touch the tables it uses.
The SQL standard describes them (SQL/PSM), but each database has its own language: PL/pgSQL in PostgreSQL, T-SQL in SQL Server, PL/SQL in Oracle, and MySQL's own procedural SQL. SQLite has no stored procedures or functions at all. Applications register functions in their own programming language through the SQLite C API or a driver instead.
-- PostgreSQL CREATE PROCEDURE archive_old_orders(cutoff DATE) LANGUAGE sql AS $$ DELETE FROM orders WHERE ordered_at < cutoff AND status = 'delivered'; $$; CALL archive_old_orders('2023-01-01');
Dialect syntax
-- PostgreSQL (11+ for procedures) CREATE FUNCTION order_total(p_order_id INT) RETURNS NUMERIC LANGUAGE sql STABLE AS $$ SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = p_order_id; $$; SELECT id, order_total(id) FROM orders; -- MySQL DELIMITER // CREATE PROCEDURE restock(IN p_product INT, IN p_amount INT) BEGIN UPDATE products SET stock = stock + p_amount WHERE id = p_product; END // DELIMITER ; CALL restock(1, 10); -- SQL Server (T-SQL) CREATE PROCEDURE dbo.restock @product_id INT, @amount INT AS BEGIN SET NOCOUNT ON; UPDATE dbo.products SET stock = stock + @amount WHERE id = @product_id; END; GO EXEC dbo.restock @product_id = 1, @amount = 10; -- Oracle (PL/SQL) CREATE OR REPLACE PROCEDURE restock(p_product IN NUMBER, p_amount IN NUMBER) AS BEGIN UPDATE products SET stock = stock + p_amount WHERE id = p_product; END; / BEGIN restock(1, 10); END;
| Topic | PostgreSQL | MySQL | SQL Server | Oracle | SQLite |
|---|---|---|---|---|---|
| Procedures | CREATE PROCEDURE, CALL |
CREATE PROCEDURE, CALL |
CREATE PROCEDURE, EXEC |
CREATE PROCEDURE, EXEC/CALL |
None |
| Functions | CREATE FUNCTION |
CREATE FUNCTION |
Scalar and table-valued | CREATE FUNCTION |
Registered by the application |
| Language | SQL, PL/pgSQL, others | MySQL SQL/PSM | T-SQL | PL/SQL | Not applicable |
| Parameters | IN, OUT, INOUT |
IN, OUT, INOUT |
@name, OUTPUT |
IN, OUT, IN OUT |
Not applicable |
| Transactions inside | Procedures may COMMIT |
Allowed | Allowed | Allowed | Not applicable |
Replacements in SQLite
Most of what a procedure does can be expressed with a view, a trigger or application code. These run in the browser.
Press Run (or Ctrl + Enter) to see the result here.
A view takes no parameters. To emulate one, store the parameter in a one-row table and read it from the view:
Press Run (or Ctrl + Enter) to see the result here.
For actions, a trigger can run statements automatically when data changes (see trigger patterns). Anything with loops or branching moves to the application. Other databases' parameterized queries (? placeholders) are the application-side counterpart of procedure parameters.
Usage notes
- Benefits. Fewer round trips for multi-step work, one place for rules, permission control, and cached plans.
- Costs. Logic hides in the database, versioning and testing are harder, and the language ties you to one vendor. Debugging tools are limited compared to application code.
- Procedures vs functions. Functions return a value and are used in expressions; procedures perform actions, can return several result sets or
OUTvalues, and are called withCALL/EXEC. Functions used in aWHEREclause can prevent index use (sargable predicates). - Security. Dynamic SQL built from strings inside a procedure is as injectable as application code; use bind parameters (
EXECUTE ... USING,sp_executesql). - Migrations. Treat procedure definitions as code: keep them in version control and deploy with
CREATE OR REPLACE/ALTER. - Not portable. Expect to rewrite them when changing database vendor.