Procedural SQL

Gnok procedures combine SQL statements with variables, decisions, loops, and error handling. They run on the engine and can return a scalar value or a table. Use them when a workflow needs several ordered steps: prepare orders for dispatch, check a data load before publishing it, or coordinate an analytical refresh.

Keep transformations in set-based SQL where possible. A procedure is useful for coordinating those statements; looping over every row is usually unnecessary for an operation that one UPDATE, INSERT … SELECT, or aggregation can express.

Start with an order-processing workflow

The order-processing tutorial uses four synthetic orders. It attempts an update, deliberately rolls it back, commits a second attempt, and returns the ready orders. Download the SQL from the tutorial page and paste it into a new Studio worksheet, then choose Run All.

Choose how to execute

Form	Use it for	Lifetime
CREATE PROCEDURE … AS $$ … $$ followed by CALL	A reusable operation with a named contract	Definition stored in the catalog
WITH name AS PROCEDURE … CALL name(…)	A one-off calculation or script	Definition exists for that statement
Ordinary SQL	A query or set-based transformation	One statement

The body language is SQL with Snowflake-Scripting-style constructs. This is not a claim of full Snowflake compatibility, and JavaScript or Python procedure bodies are not supported.

The building blocks

• Parameters and local variables: typed inputs, trailing default arguments, assignments, and scalar returns.
• Control flow: IF, CASE, loops, and nested blocks. See Variables and control flow.
• SQL integration: :name binds a variable as a SQL value; SELECT … INTO stores a single result row in variables.
• Transactions and errors: explicit transaction boundaries, declared exceptions, handlers, and rethrowing. See Transactions and execution rights.
• Tabular output: assign a query to a RESULTSET and use RETURN TABLE(...).
• Additional scripting constructs: cursors, dynamic SQL, output parameters, overloads, and nested procedure calls are described in the stored-procedure SQL reference.

Execution rights

Use EXECUTE AS CALLER for user-facing demos. A shared SQL template does not grant access to the tables or procedures it names. Each reader executes with their own connection, tenant, and permissions. Creating a procedure and executing it are separate permission checks.

In Studio, keep each dollar-quoted body intact. Run All submits the worksheet's top-level statements in sequence and gives each a result tab. Run executes the selection or current statement; select the complete definition when creating a procedure.

Share worksheets

To share a worksheet, save it and share it with Team or Public visibility. Other users then see it in their Saved tab under Shared with me, a single flat list; hovering over a query shows its author and tags. Add descriptive tags so readers can find it with Search saved queries..., which matches names, SQL text, and tags. Folders organize only your own saved queries; they aren't shown to readers of a shared query.