Routines

Routines are one of the reasons this engine exists. An application written for a full database server tends to have some of its logic in the database, and an embedded engine without them cannot stand in for one.

Both kinds are SQL only. There is no external code, no assembly loading and no LANGUAGE other than SQL.

Functions

A function body is one expression. It takes parameters, returns a scalar, and is callable anywhere an expression may appear.

CREATE FUNCTION Doubled(N INT) RETURNS INT AS
BEGIN
    RETURN N * 2;
END;

SELECT Doubled(Price) FROM Orders WHERE Doubled(Price) > 100;

Anywhere means anywhere: a select list, a WHERE, an ORDER BY, a CHECK, a computed column, a DEFAULT, an index key, a view, a trigger body.

CREATE TABLE Items (
    Id       INT PRIMARY KEY,
    Price    INT CHECK (Doubled(Price) < 1000),
    Doubled2 AS (Doubled(Price)),
    Tax      INT DEFAULT (Doubled(5))
);

CREATE INDEX IX_Doubled ON Orders ((Doubled(Price)));

Because the body is an expression rather than a program, calling a function is a substitution inside the expression evaluator. No statement runs, no transaction is touched, and the call spends none of the nesting budget, which is what makes it safe in a place evaluated once per row.

The rules, all checked when the function is declared

• Every name in the body has to be a parameter. A body has no row to read a column from.
• Every function it calls has to exist, built-in or your own.
• A function may not call itself. An expression body has nothing to stop the recursion with, and an exhausted stack cannot be caught.
• LANGUAGE SQL only. No external code and no assembly loading. Anything else is refused by name, so LANGUAGE plpgsql gets a message rather than a parse error.
• The declared RETURNS type is applied to the result, through the same conversion a column write uses. NULL stays NULL.

Determinism, and which functions may key an index

A function is deterministic when its body reads no table and calls nothing whose answer moves: NOW(), RANDOM(), NEWGUID(), a sequence. That is decided from the body when the function is declared, reported as IS_DETERMINISTIC in INFORMATION_SCHEMA.ROUTINES, and it composes, so a function calling a non-deterministic one is non-deterministic.

Only a deterministic function may key an index, because an index key is computed once when the row is written and never recomputed.

Dropping one

DROP FUNCTION [IF EXISTS] Doubled;

Refused while anything still names it: a CHECK, a computed column, a DEFAULT, an index expression, a view, a procedure body, another function. There is no CASCADE, because a schema expression left naming a function that has gone makes the object it belongs to unusable.

Procedures

A procedure body is a sequence of statements, invoked as one unit of work.

CREATE PROCEDURE ArchiveOrder(OrderId INT) AS
BEGIN
    INSERT INTO OrdersArchive SELECT * FROM Orders WHERE Id = OrderId;
    DELETE FROM Orders WHERE Id = OrderId;
END;

CALL ArchiveOrder(42);

The parameter list can be left off entirely:

CREATE PROCEDURE RecentOrders AS
BEGIN
    SELECT * FROM Orders ORDER BY CreatedAt DESC;
END;

CALL RecentOrders();

The last statement's result is the call's result, so a body ending in a SELECT returns rows. One result set only: a body with two SELECTs hands back the second.

From ADO.NET, CommandType.StoredProcedure with the routine name as the command text; see the ADO.NET provider.

DROP PROCEDURE [IF EXISTS] ArchiveOrder;

A CALL is atomic

One CALL is one statement to its caller, and therefore one unit of work: a body that fails partway leaves nothing behind, including any DDL it had already run. That is worth knowing, because DDL outside a routine is not undone by a rollback. Inside a procedure it is.

What a body may contain

SELECT, INSERT, UPDATE, DELETE, MERGE, DDL, and CALL of another procedure.

Recursion is allowed and bounded. A procedure may call itself, and statements nest at most 32 deep, after which the call is refused with an error you can catch.

What a body may not contain

Transaction control. BEGIN TRANSACTION, COMMIT, ROLLBACK and SAVEPOINT are refused when the procedure is declared. The reason is worse than untidiness: a nested COMMIT commits the calling statement's transaction, so the rest of that statement runs outside one, and nothing reports it. Measured as a three-row INSERT leaving two rows behind after its third failed, raising only the key violation. DDL fails loudly; this does not fail at all.

Declaring or dropping a routine. A body that rewrites the catalogue it is being run from.

No control flow. There is no IF, no WHILE, no loop and no local variables. Branching belongs in the application or in a CASE expression inside a statement.

No OUT parameters and no multiple result sets. Parameters go in; the last statement's result comes out.

A trigger may not CALL a procedure

This is what lets a procedure contain DDL at all. A CALL at the top level is a statement, while a trigger runs inside a loop over rows, and DDL against the object that loop is walking is not something the engine survives: a DROP TABLE fired from a trigger on the table being written would report success and destroy it.

Where routines fit

The nesting limit of 32 applies to statements, so a procedure calling something that writes to a table with a trigger that writes elsewhere stops with a catchable error rather than exhausting the stack.

A function is known to the engine once the catalogue has it, so it is refused at write time in the same way a misspelled built-in is. That applies inside a CHECK, a DEFAULT, a computed column and an index expression too.

Routines appear in INFORMATION_SCHEMA, which is how scaffolding and tooling find them:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES;
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS;

Choosing between the two

A function when you want a value, especially one that appears in several queries: a formatting rule, a derived field, a piece of arithmetic your application repeats. It composes into expressions and costs nothing beyond evaluating it.

A procedure when you want several statements to happen together and want the database to own that sequence rather than the application. Archiving a row, applying a state change with its audit entry, anything where doing half of it would be wrong.

Neither is a place to put a program. The body of each is deliberately small, and logic that needs branching belongs where branching exists.

Where to go next

• Functions, the built-ins yours sit alongside
• DDL, triggers, which have similar rules and tighter ones
• Transactions, wrapping a CALL
• ADO.NET provider, CommandType.StoredProcedure