Order-processing tutorial

A dispatch team wants to prepare new orders worth at least 100 units. Orders on hold must stay on hold, and a failed preparation attempt must leave the queue unchanged. This small workflow shows how a SQL procedure can coordinate that state transition.

Run in Studio

This example is not in Studio's shared tutorials. Download the complete worksheet, or copy it from the end of this page, and paste it into a new SQL editor tab in Studio. Choose Run All. Keep Continue on error off. Each top-level statement gets a result tab, including the procedure calls. To keep your own copy, save the tab with a name of your choice.

Use an account allowed to create objects in its tutorial catalog and to execute procedures and read/update the demo table. The worksheet creates tutorial.procedures if needed. It uses synthetic data and has no external AI-service dependency. Run All replaces tutorial.procedures.demo_orders each time; change the catalog/schema consistently if that name is already used for your own work. Users in the same tenant should use separate demo schemas if running concurrently.

Understand the workflow

1. Create four orders: two eligible new orders, one new order below the threshold, and one held order.
2. Define prepare_orders. Its min_amount input selects eligible orders; simulate_failure defaults to FALSE. SELECT … INTO records the count, and a set-based UPDATE changes their status inside an explicit transaction.
3. Call it with TRUE. A declared exception forces rollback and returns -1. Inspect the next result tab to see the original rows.
4. Call it with the default flag. It commits two updates and returns 2. Call it again: there are no remaining eligible new orders, so it returns 0.
5. Define and call ready_orders, which returns a table through a RESULTSET.
6. Run an anonymous procedure to demonstrate a local accumulator and a bounded FOR loop.

Expected results

Statement	Result to inspect
Insert fixture	Four affected rows
Forced-failure call	-1
Query after rollback	IDs 1, 2, and 4 remain new; ID 3 remains hold
First successful call	2
Repeat successful call	0
ready_orders()	(1, 120) and (4, 200)
Anonymous sum_to(10)	55
Final table query	Rows shown below

id	amount	status
1	120	ready
2	35	new
3	80	hold
4	200	ready

The held order and below-threshold order retain their original status. The repeat call changes no further rows. See Transactions and execution rights for the distinction between this single-worker example and a concurrent production queue.

Complete worksheet

-- Synthetic order workflow. Run All resets only this demo table.
CREATE CATALOG IF NOT EXISTS tutorial;
CREATE SCHEMA IF NOT EXISTS tutorial.procedures;
CREATE OR REPLACE TABLE tutorial.procedures.demo_orders (id INT, amount INT, status VARCHAR);
INSERT INTO tutorial.procedures.demo_orders VALUES (1,120,'new'),(2,35,'new'),(3,80,'hold'),(4,200,'new');
CREATE OR REPLACE PROCEDURE tutorial.procedures.prepare_orders(min_amount INT, simulate_failure BOOLEAN DEFAULT FALSE)
RETURNS INT LANGUAGE SQL EXECUTE AS CALLER AS $$
DECLARE changed INT DEFAULT 0;
 forced_failure EXCEPTION (-20001, 'Demonstration rollback');
BEGIN
 BEGIN TRANSACTION;
 SELECT COUNT(*) INTO :changed FROM tutorial.procedures.demo_orders WHERE status = 'new' AND amount >= :min_amount;
 UPDATE tutorial.procedures.demo_orders SET status = 'ready' WHERE status = 'new' AND amount >= :min_amount;
 IF simulate_failure THEN RAISE forced_failure; END IF;
 COMMIT;
 RETURN changed;
EXCEPTION
 WHEN forced_failure THEN ROLLBACK; RETURN -1;
 WHEN OTHER THEN ROLLBACK; RAISE;
END;
$$;
-- Returns -1; the two attempted updates must roll back.
CALL tutorial.procedures.prepare_orders(100, TRUE);
SELECT id, amount, status FROM tutorial.procedures.demo_orders ORDER BY id;
-- Returns 2, then 0: a repeated call does not process ready orders again.
CALL tutorial.procedures.prepare_orders(100);
CALL tutorial.procedures.prepare_orders(100);
CREATE OR REPLACE PROCEDURE tutorial.procedures.ready_orders()
RETURNS TABLE(id INT, amount INT) LANGUAGE SQL EXECUTE AS CALLER AS $$
DECLARE rs RESULTSET;
BEGIN
 rs := (SELECT id, amount FROM tutorial.procedures.demo_orders WHERE status = 'ready' ORDER BY id);
 RETURN TABLE(rs);
END;
$$;
-- Exactly (1,120) and (4,200).
CALL tutorial.procedures.ready_orders();
-- An anonymous procedure: variables and a bounded FOR loop, without persisting a definition.
WITH sum_to AS PROCEDURE (n INT) RETURNS INT LANGUAGE SQL AS $$
DECLARE total INT DEFAULT 0;
BEGIN
 FOR i IN 1 TO n DO total := total + i; END FOR;
 RETURN total;
END;
$$ CALL sum_to(10);
SELECT id, amount, status FROM tutorial.procedures.demo_orders ORDER BY id;

Next steps

Change the threshold to 150 after resetting the fixture: only order 4 should become ready. Remove the simulated failure flag in an application-facing API and propagate unexpected errors to its caller. To build a production job around this pattern, add input validation, a durable job identifier, and concurrency controls appropriate to your workload.

Read Variables and control flow for the binding and result-set syntax, or the SQL reference for procedure definitions, overloads, and additional scripting constructs.