A stored procedure is a named block of SQL and procedural logic saved inside the database, which clients run by calling it rather than sending the statements themselves. Where a view stores a query, a stored procedure stores a program: it can take parameters, hold variables, loop, branch, and modify data.
How they work
You create a procedure once, and from then on any client with permission can invoke it by name. In PostgreSQL:
CREATE PROCEDURE archive_stale_carts()
LANGUAGE SQL
AS $$
DELETE FROM shopping_carts
WHERE updated_at < now() - INTERVAL '90 days';
$$;
CALL archive_stale_carts();
The procedural language varies by database — PL/pgSQL in Postgres, T-SQL in SQL Server, PL/SQL in Oracle, and each has its own dialect for variables and control flow. That’s a recurring theme with stored procedures: they’re deeply tied to one database’s flavor, and porting a library of them to another database is a rewrite, not a migration.
The classic arguments for them come from application engineering. The logic runs next to the data, so a multi-step operation doesn’t ping-pong over the network. They give database administrators a controlled surface — clients get permission to call process_refund() without getting write access to the underlying tables. In transactional systems with strict integrity rules, that control is genuinely valuable.
Why analytics tools mostly avoid them
BI tools are built around a different contract: send a SELECT, get back a result set, know what columns to expect. Stored procedures break that contract in several ways. A procedure is opaque from the outside — the tool can’t inspect what it will return, or whether calling it is even safe, since a procedure named like a report might also write data. Its logic is invisible to anything reading the schema, so column lineage and metadata syncing stop at the call site. And because invocation syntax and result behavior vary by database, there’s no portable way for a tool to work with them generically.
So most analytics tools, Metabase included, don’t call stored procedures. The analytics-friendly homes for reusable logic are declarative ones: a view or materialized view in the database, a transformation layer like dbt, or, in Metabase, a model — a saved, curated query others build on — and SQL snippets for reusable fragments in the native query editor. If logic your reports need currently lives in a procedure, the usual move is to expose its query as a view the BI tool can read.
That doesn’t make stored procedures obsolete — they still earn their keep for operational work like scheduled cleanup jobs or guarded write paths. The dividing line is direction: procedures are for doing things to the data, and analytics is about reading it.
Related terms
Further reading
Put it to work
- Best practices for writing SQL queries — Tutorial
- PostgreSQL monitoring dashboard — Dashboard
