Skip to content
Tutorials Center
My learning

Topics

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.

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

SQL
-- 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.

SQL A view as a stored query ยท database: shop

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:

SQL A parameter table feeding a view ยท database: shop

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 OUT values, and are called with CALL/EXEC. Functions used in a WHERE clause 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.