Stored procedures and functions

What they are

So far every query you wrote was sent by you (or an app) one statement at a time. A stored procedure is different: a named piece of code that lives inside the database itself, with parameters, variables, IF and loops. You create it once, and afterwards anyone with permission runs it by name. A stored function is similar but returns a value, so you can use it inside a SELECT.

Why teams use them

• Logic near the data: a ten-step job (check stock, create the order, reduce stock) runs inside the server, without sending each step over the network. Fewer round trips means less waiting.
• One place for a rule: every app that calls place_order gets the same checks, whether it is the website, the mobile app or a nightly script.
• Permissions: you can let a user EXECUTE a procedure without giving them UPDATE on the tables underneath. That is common in banks and older enterprise systems.

Why many teams avoid them

• Harder to test and debug than normal application code. Unit-test tools, debuggers and code review are weaker inside the database.
• Harder to version: the code lives in the database, so you must keep the CREATE scripts in Git and apply them with migrations, or production drifts from what the team thinks it runs.
• Vendor lock-in: PL/pgSQL, MySQL procedures and T-SQL are three different languages. Moving engines means rewriting them.
• Hidden logic: a developer reading the app may not know that a procedure also changes a second table.

A balanced rule: keep business rules in application code by default, and use procedures or functions for data-heavy steps that clearly benefit from running next to the data, or when your organisation requires access only through procedures.

PostgreSQL: a function in PL/pgSQL

PL/pgSQL is PostgreSQL's procedural language. The body is written between $$ marks, which are just quotes that let you use single quotes freely inside.

-- PostgreSQL only
CREATE OR REPLACE FUNCTION customer_total(p_customer INT)
RETURNS BIGINT
LANGUAGE plpgsql
AS $$
DECLARE
  v_total BIGINT;
BEGIN
  SELECT SUM(oi.qty * oi.price) INTO v_total
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  WHERE o.customer_id = p_customer AND o.status = 'Delivered';
  RETURN COALESCE(v_total, 0);
END;
$$;

SELECT customer_total(1);   -- 4028
SELECT customer_total(3);   -- 0 (Priya's only order was cancelled)

PostgreSQL 11 added real procedures with CREATE PROCEDURE. A procedure returns nothing by default, is run with CALL, and, unlike a function, may COMMIT inside its body.

-- PostgreSQL 11+ only
CREATE PROCEDURE mark_delivered(p_order INT)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE orders SET status = 'Delivered' WHERE id = p_order;
END;
$$;

CALL mark_delivered(105);

MySQL: DELIMITER and CREATE PROCEDURE

The body of a MySQL procedure contains semicolons, but the mysql command-line client normally treats a semicolon as "run this now". DELIMITER changes that marker for a moment, so the whole CREATE PROCEDURE is sent as one statement. OUT parameters hand a result back in a session variable such as @total.

-- MySQL only
DELIMITER //
CREATE PROCEDURE customer_total(IN p_customer INT, OUT p_total BIGINT)
BEGIN
  SELECT COALESCE(SUM(oi.qty * oi.price), 0) INTO p_total
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  WHERE o.customer_id = p_customer AND o.status = 'Delivered';
END //
DELIMITER ;

CALL customer_total(1, @total);
SELECT @total;   -- 4028

SQL Server: T-SQL with @parameters and EXEC

-- SQL Server only
CREATE PROCEDURE dbo.CustomerTotal
  @CustomerId INT
AS
BEGIN
  SET NOCOUNT ON;
  SELECT COALESCE(SUM(oi.qty * oi.price), 0) AS total
  FROM orders AS o JOIN order_items AS oi ON oi.order_id = o.id
  WHERE o.customer_id = @CustomerId AND o.status = 'Delivered';
END;
GO

EXEC dbo.CustomerTotal @CustomerId = 1;   -- total: 4028

SQLite: no stored procedures

SQLite runs inside your app, so it has no server to store procedures on. Apps keep that logic in application code. If you need a custom function inside SQL, the app registers a user-defined function on its connection, for example with Python's sqlite3 module:

import sqlite3
con = sqlite3.connect(':memory:')
con.create_function('gst', 1, lambda price: round(price * 1.18, 2))
print(con.execute('SELECT gst(899)').fetchone())
# Output: (1060.82,)

Common mistakes

• Keeping procedure code only in the database. Fix: store every CREATE OR REPLACE script in Git and deploy it with migrations.
• Forgetting DELIMITER in the MySQL client, so the CREATE stops at the first semicolon with a syntax error. Fix: wrap it in DELIMITER // ... // DELIMITER ;.
• Building SQL by gluing strings inside a procedure (dynamic SQL). It is just as open to SQL injection as app code. Fix: pass values as parameters (USING in PL/pgSQL, sp_executesql in SQL Server).
• Returning NULL when there are no rows. Fix: wrap SUM in COALESCE(..., 0) when callers expect a number.