Scripting and multi-statement queries in BigQuery

A BigQuery script is several statements run as one job, with variables, IF, loops, error handling and dynamic SQL. Only the last statement's result is returned, each statement is billed separately, and DECLARE must come first.

A script, which Google calls a multi-statement query, is several SQL statements separated by semicolons and sent as one job. Scripts add variables (DECLARE, SET), branching (IF), loops (LOOP, WHILE, FOR ... IN), error handling (BEGIN ... EXCEPTION ... END) and dynamic SQL (EXECUTE IMMEDIATE). They are how you write a multi-step transformation, a backfill loop or a data check without an outside orchestrator.

Variables

DECLARE creates a variable with a type and an optional default, and SET changes it:

DECLARE cutoff DATE DEFAULT DATE '2026-03-01';
DECLARE min_amount NUMERIC;
SET min_amount = 100;
SELECT cutoff, min_amount, DATE_SUB(cutoff, INTERVAL 7 DAY) AS week_before

SET (a, b) = (SELECT AS STRUCT ...) fills several variables from one query row:

DECLARE top_customer STRING;
DECLARE top_total INT64;
SET (top_customer, top_total) = (
  SELECT AS STRUCT customer, SUM(amount)
  FROM UNNEST([STRUCT('ana' AS customer, 30 AS amount), ('ben', 50), ('ana', 40)])
  GROUP BY customer
  ORDER BY 2 DESC
  LIMIT 1
);
SELECT top_customer, top_total

A variable declared without a default is NULL. DECLARE x DEFAULT expr without a type takes the type of the expression.

DECLARE comes first

All DECLARE statements must come at the start of the script or of a BEGIN ... END block. BigQuery checks this before running anything:

SELECT 1;
DECLARE x INT64;
SELECT x

To declare a variable later, open a block with BEGIN and declare it at the top of that block.

IF

DECLARE row_count INT64 DEFAULT (SELECT COUNT(*) FROM UNNEST([1, 2, 3]));
IF row_count = 0 THEN
  SELECT 'empty' AS status;
ELSEIF row_count < 10 THEN
  SELECT 'small' AS status, row_count;
ELSE
  SELECT 'large' AS status, row_count;
END IF

Only the branch that runs is executed and billed.

Loops

WHILE repeats while a condition holds. LOOP ... END LOOP repeats until BREAK (or LEAVE), and CONTINUE (or ITERATE) skips to the next round:

CREATE TEMP TABLE days (day DATE);
BEGIN
  DECLARE d DATE DEFAULT DATE '2026-03-01';
  WHILE d <= DATE '2026-03-04' DO
    INSERT INTO days VALUES (d);
    SET d = DATE_ADD(d, INTERVAL 1 DAY);
  END WHILE;
END;
SELECT day FROM days ORDER BY day

FOR ... IN (query) runs the body once per row of a query, with the row available as a struct:

CREATE TEMP TABLE log (message STRING);
FOR region IN (SELECT name FROM UNNEST(['eu', 'us', 'asia']) AS name) DO
  INSERT INTO log VALUES (CONCAT('processed ', region.name));
END FOR;
SELECT message FROM log ORDER BY message

Each statement in a loop body is its own job. A loop that runs a query 1,000 times is billed for 1,000 queries, each with the 10 MB minimum per table. Before you write a loop, check whether one set-based statement can do the same. Loops are for work that really is sequential: one partition at a time, retries, or steps that depend on earlier results.

Only the last result comes back

A script returns the result of its last statement that produces one. Earlier results are still computed and billed, but you only see them in the job’s child jobs:

SELECT 'first' AS step;
SELECT 'second' AS step;
SELECT 'third' AS step

Handling errors

A BEGIN ... EXCEPTION WHEN ERROR THEN ... END block catches any error raised inside it. @@error.message holds the message and @@error.statement_text the failing statement:

DECLARE result STRING;
BEGIN
  SELECT CAST('n/a' AS INT64);
  SET result = 'converted';
EXCEPTION WHEN ERROR THEN
  SET result = CONCAT('failed: ', @@error.message);
END;
SELECT result

The message inside a script starts with Query error: and ends with the position in the script.

To stop a script on purpose, use RAISE. Without an EXCEPTION block around it, the script fails with your message:

DECLARE missing INT64 DEFAULT 3;
IF missing > 0 THEN
  RAISE USING MESSAGE = FORMAT('%d orders have no customer', missing);
END IF;
SELECT 'never reached' AS note

ASSERT condition AS 'message' is the short form for a data check that should stop the run.

Dynamic SQL

EXECUTE IMMEDIATE runs a SQL string built at run time, and INTO stores its single-row result in variables:

DECLARE col STRING DEFAULT 'amount';
DECLARE total INT64;
CREATE TEMP TABLE orders AS
SELECT 1 AS id, 30 AS amount UNION ALL SELECT 2, 45;
EXECUTE IMMEDIATE FORMAT('SELECT SUM(%s) FROM orders', col) INTO total;
SELECT total

Use it when a table or column name has to be computed, since query parameters can’t stand in for identifiers. Pass values with USING rather than pasting them into the string: EXECUTE IMMEDIATE 'SELECT * FROM t WHERE id = ?' USING my_id.

Temporary tables

CREATE TEMP TABLE makes a table that only this script can use. It is the usual way to pass data between steps. BigQuery keeps it, and bills its storage, for up to 24 hours after the script ends unless you drop it. In Querylab.io a script that creates a temp table and never drops it gets a warning on the CREATE, with a quick fix that appends DROP TABLE IF EXISTS (multi-statement queries). Temp tables are only allowed inside a script or a session. See Temporary tables.

How a script runs and is billed

• The script is one parent job of type SCRIPT. Each statement that does work becomes a child job. DECLARE and SET with plain values create no child job.
• Bytes are billed per statement and added up. Two statements that each read word from samples.shakespeare are estimated at 2.5 MB, twice the 1.3 MB of one, while one query that reads the column twice pays once.
• A dry run of a script is only reliable when every statement reads existing tables. When the script creates a temp table, BigQuery stops counting at that statement and reports a “lower bound”: a script that starts with CREATE TEMP TABLE dry-runs as a lower bound of 0 bytes, even if a later statement reads gigabytes. Querylab.io estimates CREATE TEMP TABLE ... AS SELECT from its SELECT, and the status bar estimate covers the whole script (cost limits).
• Each statement commits on its own. If statement 5 fails, statements 1 to 4 stay done. Wrap statements in a transaction to make them all-or-nothing.
• Each statement can run for up to 6 hours and the whole script for up to 24 hours. A project can run up to 1,000 scripts at the same time.

In Querylab.io a script shows a result for every statement, not only the last one. See Multi-statement queries.