Stored Procedures and Functions

Up to this point, the transaction’s boundary, what to do on error, and retrying were all decided by application code; the database only took the statements it was sent. Some of this logic can also be defined inside the database itself. If the “lend a book” rule is put into a procedure, the application sends one call instead of two statements; the rule stays in the same place as the data.

A stored procedure and a stored function are code units stored on the server side and called by name. This lesson covers their standard form, the distinction between them, and the trade-offs they bring.

Procedure and Function

What separates them is how they are called and what they return.

A procedure is called with CALL. It is not required to return a value; it can take input, output, and input-output parameters, and its body can contain statements that change data.

A function returns a value and can be used anywhere an expression is allowed — in a SELECT list, in a WHERE condition. It is therefore expected to have no side effects.

The procedural subset of standard SQL writes the lending rule like this:

CREATE PROCEDURE lend_book(IN p_book_id INTEGER, IN p_member_id INTEGER)
LANGUAGE SQL
BEGIN ATOMIC
  INSERT INTO loan(book_id, member_id, pickup)
    VALUES (p_book_id, p_member_id, CURRENT_DATE);
  UPDATE book SET on_shelf = 0 WHERE id = p_book_id;
END;

Its call is one line:

CALL lend_book(1, 4);

The late-days calculation, by contrast, is a function: it returns a value and is used in a query:

CREATE FUNCTION late_days(p_pickup DATE, p_returned DATE)
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
RETURN CASE
         WHEN (COALESCE(p_returned, CURRENT_DATE) - p_pickup) - 14 > 0
         THEN (COALESCE(p_returned, CURRENT_DATE) - p_pickup) - 14
         ELSE 0
       END;

These three blocks are not executed; they are written to show the form. The reason is that support for the procedural subset varies by engine: some offer a language close to the standard, some use their own procedural language, and some — including this course’s observation environment — do not support stored procedures at all. What does not change is the distinction between CREATE PROCEDURE and CREATE FUNCTION and the CALL form of invocation.

The BEGIN ATOMIC block states that the statements in the body form an indivisible unit: if one fails, none of them apply. The atomicity from the transaction control lesson becomes part of the procedure’s definition here.

A Function Defined in the Application

On an engine that does not support stored procedures, the counterpart of a function is an application function registered on the connection. The code lives in the application, but it runs inside the query — the engine calls it for every row:

cat > function-in-app.mjs <<'JS'
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec(`
  CREATE TABLE loan(id INTEGER PRIMARY KEY, member_id INT, pickup TEXT, returned TEXT);
  INSERT INTO loan VALUES (1,1,'2024-03-01','2024-03-15'),(2,1,'2024-03-04','2024-03-18'),
    (3,2,'2024-03-06','2024-03-25'),(4,2,'2024-03-11',NULL),(5,7,'2024-03-18','2024-03-24');
`);

const DAY = 86400000;
db.function('late_days', { deterministic: true }, (pickup, returned) => {
  const end = returned === null ? Date.parse('2024-03-31') : Date.parse(returned);
  const days = Math.round((end - Date.parse(pickup)) / DAY);
  return Math.max(0, days - 14);
});

for (const s of db.prepare(
  `SELECT id, member_id, pickup, COALESCE(returned, '(open)') AS returned,
          late_days(pickup, returned) AS late
   FROM loan WHERE late_days(pickup, returned) > 0 ORDER BY id`).all()) {
  console.log(JSON.stringify(s));
}
JS
node function-in-app.mjs

{"id":3,"member_id":2,"pickup":"2024-03-06","returned":"2024-03-25","late":5}
{"id":4,"member_id":2,"pickup":"2024-03-11","returned":"(open)","late":6}

Two of the five records exceeded the fourteen-day period. The rule was written in one place and used in both the SELECT list and the WHERE condition; the same calculation did not need to be duplicated in two places. This is exactly what a stored function provides — the difference is only where the code lives.

Determinism

The word DETERMINISTIC in the function definition is a promise: called with the same arguments, it always returns the same result. That promise gives the planner two permissions — caching the result instead of recomputing it, and storing the function’s result in an index.

The second permission can be measured. The script below defines two functions; both have the same body, only one declares itself deterministic. An index is attempted on each:

cat > deterministic.mjs <<'JS'
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec(`CREATE TABLE loan(id INTEGER PRIMARY KEY, pickup TEXT, returned TEXT);`);

db.function('fixed_duration', { deterministic: true },
            (pickup, returned) => (returned === null ? -1 : 1));
db.function('variable_duration', (pickup, returned) => (returned === null ? -1 : 1));

for (const [name, fn] of [['fixed_duration', 'fixed_duration'],
                          ['variable_duration', 'variable_duration']]) {
  try {
    db.exec(`CREATE INDEX index_${name} ON loan(${fn}(pickup, returned))`);
    console.log(`${name}: index created`);
  } catch (error) {
    console.log(`${name}: ${error.message}`);
  }
}
JS
node deterministic.mjs

fixed_duration: index created
variable_duration: non-deterministic functions prohibited in index expressions

The reason is simple: an index stores a function’s result. If the result can change over time, the value in the index falls out of sync with the data, and queries return the wrong answer. The engine cuts off this danger before it starts.

The same reasoning explains why a non-deterministic function gets called freshly for every row inside a query. A function that reads the current time, a random number, or session information, if declared deterministic, turns the engine’s optimizations into silent errors. Because the declaration is a promise, the responsibility falls on whoever wrote it.

The Round-Trip Cost

The most concrete benefit of server-side logic is reducing the number of round trips between the application and the engine. This cost does not depend on how much work there is: every statement means a separate request and a separate response.

The script below lends two hundred books. First it sends two statements per book, then it does the same work with two set-based statements:

cat > round-trip.mjs <<'JS'
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec(`
  CREATE TABLE book(id INTEGER PRIMARY KEY, on_shelf INT NOT NULL);
  CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT);
`);
for (let i = 1; i <= 200; i += 1) db.exec(`INSERT INTO book VALUES (${i}, 1)`);

let statements = 0;
const run = (sql, ...arg) => { statements += 1; db.prepare(sql).run(...arg); };

statements = 0;
db.exec('BEGIN');
for (let i = 1; i <= 200; i += 1) {
  run('INSERT INTO loan(book_id, member_id, pickup) VALUES (?, 4, ?)',
      i, '2024-03-11');
  run('UPDATE book SET on_shelf = 0 WHERE id = ?', i);
}
db.exec('COMMIT');
console.log('row by row   : statements sent =', statements,
            '| loan count =', db.prepare('SELECT COUNT(*) AS n FROM loan').get().n);

db.exec('DELETE FROM loan; UPDATE book SET on_shelf = 1;');
statements = 0;
db.exec('BEGIN');
run(`INSERT INTO loan(book_id, member_id, pickup)
     SELECT id, 4, '2024-03-11' FROM book WHERE on_shelf = 1`);
run('UPDATE book SET on_shelf = 0 WHERE on_shelf = 1');
db.exec('COMMIT');
console.log('set-based    : statements sent =', statements,
            '| loan count =', db.prepare('SELECT COUNT(*) AS n FROM loan').get().n);
JS
node round-trip.mjs

row by row   : statements sent = 400 | loan count = 200
set-based    : statements sent = 2 | loan count = 200

The same two hundred records were produced with 2 statements instead of 400. Because the engine and the application share a process here, the effect of the difference is small; in an application talking over a network, 400 statements mean 400 round trips, and the latency of each round trip alone can exceed the cost of the work itself.

This is what a stored procedure earns: the loop stays on the server side, the application makes a single call. But the measurement says something else too — cutting round trips does not require a procedure. The set-based statement achieved the same gain without any stored procedure. A procedure can only be justified by this reasoning when a control flow is needed that cannot be expressed with a set-based statement.

Comparison with a View

Procedures are not the only thing put on the server side. A view names a query and stores it in the schema; it takes no parameters, but it collects repeated join and filter logic in one place:

sqlite3 -box -header <<'SQL'
CREATE TABLE member(id INTEGER PRIMARY KEY, name TEXT, branch TEXT);
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO member VALUES (1,'Alice','Kadikoy'),(2,'Ben','Kadikoy'),(7,'Grace','Kadikoy');
INSERT INTO loan VALUES (1,1,1,'2024-03-01','2024-03-15'),(4,7,1,'2024-03-11',NULL),
  (9,8,2,'2024-03-21',NULL),(23,2,7,'2024-03-11','2024-03-16');

CREATE VIEW open_loans AS
  SELECT l.id, m.name, l.pickup
  FROM loan l JOIN member m ON m.id = l.member_id
  WHERE l.returned IS NULL;

SELECT * FROM open_loans ORDER BY id;
SELECT COUNT(*) AS open_count FROM open_loans;
SQL

┌────┬───────┬────────────┐
│ id │ name  │   pickup   │
├────┼───────┼────────────┤
│ 4  │ Alice │ 2024-03-11 │
│ 9  │ Ben   │ 2024-03-21 │
└────┴───────┴────────────┘
┌────────────┐
│ open_count │
├────────────┤
│ 2          │
└────────────┘

The definition of an “open loan” stood in one place, and both queries used it. If the definition changes — say, canceled records should also be excluded — a single place gets updated. Unlike a common table expression, a view is persistent and part of the schema.

Putting code on the server side has a cost too. Procedure bodies fit into version control and test suites with more friction than application code does; since every engine’s procedural language differs, portability is lost; debugging tools are more limited than for the application language. The measure is drawn from the nature of the logic placed there: rules that protect data integrity gain from staying close to the data, workflow decisions gain from staying in the application.

Summary

• A procedure is called with CALL and can change data; a function returns a value and is used inside an expression.
• The standard procedural subset is defined with CREATE PROCEDURE and CREATE FUNCTION; the degree of support varies by engine.
• A DETERMINISTIC declaration grants the planner caching and indexing permission; a non-deterministic function cannot be used in an index expression.
• Server-side logic reduces the round-trip count; in the measurement, 400 statements of work dropped to 2, but the same gain was reached with a set-based statement and no procedure.
• A view is a parameterless server-side abstraction that keeps repeated query logic in one place in the schema.

Next Step

In this lesson, server-side code was always called: the procedure by CALL, the function by name inside a query. Because it was read from where it was called, what it did was visible. The database also defines code that runs without being called: triggers, which fire on their own when a table is written to. The next lesson covers how they are defined, what they are good for, and why code that runs without being called deserves special care.