> ## Documentation Index > Fetch the complete documentation index at: https://docs.wirekite.io/llms.txt > Use this file to discover all available pages before exploring further. # Stored procedures > Name a program, store it in the database, and run it from anywhere with EXEC — compiled once per host, with automatic recompiles when source, toolchain, or schema change. A file submitted with `@` is compile-and-run: nothing persists. A **stored procedure** is the persistent form — the same program, named, stored **in the target database itself**, and runnable from any host with `EXEC name(...)`. Procedures are how scheduled and repeated work ships: compile once, run forever. ## Writing a procedure A procedure file is the `CREATE` statement itself: ```sql flag_big_orders.sumatra theme={null} CREATE OR REPLACE PROCEDURE flag_big_orders(cutoff INT) IS BEGIN FOR batch IN (SELECT order_id, customer_id, amount, status, order_date, region FROM sumatra_orders WHERE status = 'pending' AND amount >= CAST(PARAM('cutoff') AS INT)) ORDER KEY (order_id) BATCH SIZE 10000 LOOP FOR rec IN batch LOOP rec.status := 'priority'; UPDATE sumatra_orders SET ROW = rec WHERE order_id = rec.order_id; END LOOP; COMMIT; END LOOP; END; ``` The form differs from an anonymous block in three small ways: * The header is `CREATE OR REPLACE PROCEDURE name(arg TYPE, ...) IS`. Write the parentheses only when there is at least one argument — a zero-argument procedure has no `()` in its header. * Declarations come **bare between `IS` and `BEGIN`** — a procedure has no `DECLARE` keyword. * It closes with plain `END;` (not `END name;`). Arguments are positional, read-only inputs, exactly like `PARAM` declarations: reference them inside SQL as `PARAM('cutoff')`, casting non-text values at the point of use. There are no `OUT` arguments, no defaults, and no named-argument calls. A `PARAM` section inside a procedure is not allowed — the header replaces it. `RETURN;` anywhere in the body ends the run cleanly (exit 0); work committed before the `RETURN` stays committed. ## Storing it Submit the file like any other: ```text theme={null} sumatra> @flag_big_orders.sumatra [sumatra] procedure flag_big_orders created (procid 4f2a91be00c17d55) ``` Because the file's program is a `CREATE`, submitting it **stores** instead of running: the shell compiles it against the live catalog, builds it, saves the source in the database, and caches the binary on this host. Nothing executes, and a `CREATE` submission takes no arguments — argument values belong to `EXEC`. A compile error stores **nothing**. There is no "invalid procedure" state to discover at 3 a.m.: if it is stored, it compiled. ## Running it ```text theme={null} sumatra> EXEC flag_big_orders(500) Completed (exit 0) ``` `EXEC` rules: * Parentheses always — `EXEC nightly()` is the zero-argument call. * Arguments are positional literals: bare tokens (`42`, `3.14`, `2026-01-01`, `TRUE`) or single-quoted text (`'North America'`). A text value containing a quote cannot be passed in this release. * No trailing semicolon — `EXEC` is one shell statement. From a script or cron, use the one-shot form; the exit code is the run's outcome (`0` / `2` / `1`): ```bash theme={null} # crontab: reprioritize nightly at 02:00 0 2 * * * /usr/local/bin/sumatra --dsn /etc/sumatra/prod.dsn "EXEC flag_big_orders(500)" ``` ## Where things live | Location | What | Notes | | ---------------------------------------------- | ---------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------- | | `sumatra_source` table, in the target database | One row per procedure: `name`, `source`, `procid`, `updated_at` | The database is the single source of truth; any SQL client can `SELECT` from it to list procedures | | `~/.sumatra/bin//` on each host | Cached compiled binaries, named `--`, with the generated `.cpp` beside each for inspection | Purely a cache — safe to delete; the next `EXEC` recompiles from stored source | Because the source lives in the database, a procedure stored from your laptop runs from the production cron host with nothing copied around — the first `EXEC` there compiles it locally and caches the binary. ## When recompiles happen A normal `EXEC` runs the cached binary with **zero** compilation. Recompiles happen only for four reasons, and each is announced: ```text theme={null} [sumatra] compiling flag_big_orders (first run on this host) [sumatra] compiling flag_big_orders (source changed) [sumatra] compiling flag_big_orders (sumatra upgraded to 0.2.0) [sumatra] compiling flag_big_orders (schema changed) ``` The last one deserves a note: every compiled program bakes in a fingerprint of the table shapes it was compiled against, and re-checks it at run start. If the schema drifted since compile, the binary refuses to run on stale assumptions; the shell then recompiles the stored source against the current schema and reruns — once. If the fresh compile refuses again, the schema is changing underfoot and the run stops loudly. ## Managing procedures ```text theme={null} sumatra> DROP PROCEDURE flag_big_orders [sumatra] procedure flag_big_orders dropped ``` `DROP PROCEDURE` removes the database row and this host's cache entries. Dropping a name that does not exist is an error, never a silent no-op. To change a procedure, just submit the edited `CREATE OR REPLACE` file again — replace is last-write-wins. A few operational notes: * **Procedures do not call procedures.** `EXEC` is a shell statement, not a language statement — composition is sequencing several `EXEC` lines in your script. * **Duplicate runs are refused.** Two concurrent runs of the same procedure with the same argument values are refused via an engine-side lock marker, so a cron overlap cannot double-apply. * **The cache key is the database name.** If two different Firebolt accounts have same-named databases, they share a host cache directory — use distinct database names (or hosts) to keep them apart.