# 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.

Source: https://docs.querylab.io/bigquery/scripting/

Updated: 2026-10-14

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, SET and using variables**

```sql
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
```

Result in BigQuery (1 row):

| cutoff | min_amount | week_before |
| --- | --- | --- |
| 2026-03-01 | 100 | 2026-02-22 |

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

**the top customer into two variables**

```sql
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
```

Result in BigQuery (1 row):

| top_customer | top_total |
| --- | --- |
| ana | 70 |

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:

**DECLARE after a SELECT**

```sql
SELECT 1;
DECLARE x INT64;
SELECT x
```

BigQuery rejects this query:

```text
Variable declarations are allowed only at the start of a block or script at [2:1]
```

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

## IF

**IF / ELSEIF / ELSE**

```sql
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
```

Result in BigQuery (1 row):

| status | row_count |
| --- | --- |
| small | 3 |

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:

**one row per day with WHILE**

```sql
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
```

Result in BigQuery (4 rows):

| day |
| --- |
| 2026-03-01 |
| 2026-03-02 |
| 2026-03-03 |
| 2026-03-04 |

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

**FOR over a list of regions**

```sql
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
```

Result in BigQuery (3 rows):

| message |
| --- |
| processed asia |
| processed eu |
| processed us |

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:

**three SELECTs, one result**

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

Result in BigQuery (1 row):

| step |
| --- |
| third |

## 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:

**catch a failed cast**

```sql
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
```

Result in BigQuery (1 row):

| result |
| --- |
| failed: Query error: Bad int64 value: n/a at [3:3] |

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:

**RAISE with a computed message**

```sql
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
```

BigQuery rejects this query:

```text
3 orders have no customer at [3:3]
```

`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:

**sum a column whose name is in a variable**

```sql
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
```

Result in BigQuery (1 row):

| total |
| --- |
| 75 |

Use it when a table or column name has to be computed, since [query parameters](https://docs.querylab.io/bigquery/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](https://docs.querylab.io/writing-sql/multi-statement-queries/#using-temp-tables-across-statements)). Temp tables are only allowed inside a script or a [session](https://docs.querylab.io/bigquery/sessions/). See [Temporary tables](https://docs.querylab.io/bigquery/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](https://docs.querylab.io/writing-sql/cost-limits/#create-temp-table-as-select)).
- Each statement commits on its own. If statement 5 fails, statements 1 to 4 stay done. Wrap statements in a [transaction](https://docs.querylab.io/bigquery/transactions/) 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.

Run a multi-statement script with a variable in it. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Work with multi-statement queries](https://cloud.google.com/bigquery/docs/multi-statement-queries)

- [Procedural language](https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language)

- [Quotas and limits, multi-statement queries](https://cloud.google.com/bigquery/quotas#multi_statement_query_limits)