# Query parameters in BigQuery

> BigQuery query parameters are named (@name) or positional (?), carry a type, and replace values only, never table or column names. They are the way to pass user input safely, and a parameter in a partition filter prunes partitions like a literal.

Source: https://docs.querylab.io/bigquery/query-parameters/

Updated: 2026-10-14

A query parameter is a placeholder in the SQL text. The value travels separately from the query, with a type, and BigQuery never parses it as SQL. Use `@name` for a named parameter or `?` for a positional one. A query uses one style or the other, and the parameter replaces a value: a literal in `WHERE`, a `LIMIT`, an array to `UNNEST`. It can't replace a table name, a column name or a keyword.

The examples on this page show SQL and the `bq` flags. They aren't runnable on the page, because the page has no way to pass values. We ran each of them on BigQuery with `bq query`, except where noted.

## Named parameters

```sql
SELECT word, word_count
FROM `bigquery-public-data.samples.shakespeare`
WHERE corpus = @corpus
  AND word_count >= @min_count
ORDER BY word_count DESC
LIMIT @top_n
```

```bash
bq query --use_legacy_sql=false \
  --parameter=corpus:STRING:hamlet \
  --parameter=min_count:INT64:100 \
  --parameter=top_n:INT64:5 \
  'SELECT ...'
```

The flag is `--parameter=name:type:value`. A name is a plain identifier, matched without regard to case (`@A` found the parameter `a`). A parameter can appear more than once in the query, and in `LIMIT`, `IN (@a, @b)`, `LIKE @pattern`, date arithmetic and `GENERATE_ARRAY(1, @n)`.

A parameter that isn't supplied is an error:

**no value for @order_id**

```sql
SELECT order_id
FROM UNNEST([1, 2, 3]) AS order_id
WHERE order_id = @order_id
```

BigQuery rejects this query:

```text
Query parameter 'order_id' not found at [3:18]
```

Two parameters with the same name are an error ("Duplicate query parameters named a"). A parameter that the query never uses is ignored without a message, so a typo in a name shows up only as the "not found" error for the one the query does use.

## Positional parameters

With `?` the parameters are matched by order. Give them an empty name in `--parameter`:

```bash
bq query --use_legacy_sql=false \
  --parameter=::hamlet \
  --parameter=:INT64:100 \
  'SELECT word FROM `bigquery-public-data.samples.shakespeare` WHERE corpus = ? AND word_count >= ?'
```

(The first flag is `::hamlet`, name and type both empty, so the type defaults to `STRING`.) We tested the arithmetic form `SELECT ? + ?` with `--parameter=:INT64:5 --parameter=:INT64:7` and got 12.

Supplying fewer values than there are `?` marks fails with "Query parameter number 2 is not defined (1 provided)". Supplying more is ignored: three values for two marks used the first two.

Named and positional parameters can't be mixed. A query with `@a + ?` fails with "Cannot mix named and positional parameters in scripts", and `?` with a named flag, or `@a` with an unnamed flag, is rejected ("Positional parameters are not supported", "Named parameters are not supported").

## Parameters are values, not identifiers

A parameter can't stand for a table or column name:

**FROM @table_name**

```sql
SELECT *
FROM @table_name
```

BigQuery rejects this query:

```text
Query parameters cannot be used in place of table names at [2:6]
```

A parameter in the select list is just a constant. `SELECT @c AS col` with `c = orders` returns the string `orders`, and `ORDER BY @n` and `GROUP BY @u` are accepted but sort or group by a constant, which has no effect. If the column to sort by is chosen at run time, build the query text in a script with `EXECUTE IMMEDIATE`, and check the name against a fixed list first:

```sql
DECLARE sort_column STRING DEFAULT 'word_count';
ASSERT sort_column IN ('word', 'word_count');
EXECUTE IMMEDIATE FORMAT(
  'SELECT word, word_count FROM `bigquery-public-data.samples.shakespeare` ORDER BY %s DESC LIMIT 3',
  sort_column);
```

We ran this script and it returned the top three rows. `EXECUTE IMMEDIATE` also takes a `USING` clause that passes values into `?` placeholders of the dynamic statement, so only identifiers need to be formatted into the text. A top-level parameter used inside a function body is rejected ("Query parameter is not allowed in the body of SQL function"), and so is one in a view definition ("Query parameters cannot be used inside SQL view bodies"). Pass the value as a function argument instead.

## Why use them

A parameter's value is never parsed as SQL. We passed the string `it's; DROP TABLE x` as a `STRING` parameter and got it back unchanged, quote and semicolon included. Building the same query by pasting the string in would break on the quote at best. That is the reason to use parameters for anything a user types.

## NULL, arrays and structs

Google's page says a parameter value can't be `NULL`. That is wrong today: `--parameter=x:STRING:NULL` and `--parameter=x:INT64:NULL` both gave a NULL parameter. A comparison with it gives NULL like any NULL, so `WHERE col = @x` matches nothing. Use `IS NOT DISTINCT FROM @x` or `COALESCE` for "no filter" behavior.

Arrays are typed `ARRAY<type>`, and `IN UNNEST(@ids)` is the usual use:

```bash
bq query --use_legacy_sql=false \
  --parameter='ids:ARRAY<INT64>:[1,3]' \
  'SELECT x FROM UNNEST([1,2,3]) x WHERE x IN UNNEST(@ids)'
```

This returned 1 and 3. Structs are `STRUCT<a INT64, b STRING>` with a JSON value, and fields are read with `@s.a`. `TIMESTAMP`, `DATE`, `DATETIME`, `NUMERIC` and the other scalar types work as expected: a `TIMESTAMP` parameter with `2026-03-01 10:00:00+00` returned hour 10.

## Parameters and cost

A parameter in a filter on a partitioning column prunes partitions just like a literal. We dry-ran this on a public table partitioned by `datehour` (`bigquery-public-data.wikipedia.pageviews_2020`, 2.4 TB, partition filter required):

```sql
SELECT SUM(views)
FROM `bigquery-public-data.wikipedia.pageviews_2020`
WHERE datehour = @h   -- or: datehour = TIMESTAMP '2020-03-01 10:00:00'
```

Both forms reported an upper bound of 2,321,670,224 bytes, which is one hour. The `require_partition_filter` check accepted the parameter, too. See [partitioning](https://docs.querylab.io/bigquery/partitioning/) for what the estimate means.

The query cache treats a parameterized query like any other. In our test, running it again with the same value was a cache hit and a different value was a miss, so values are part of the cache key. See [query cache](https://docs.querylab.io/bigquery/query-cache/).

A parameter can be the percentage in `TABLESAMPLE SYSTEM (@p PERCENT)` and the number in `LIMIT @n`. It can be a `PIVOT` value in the `IN` list only with an explicit alias (`IN (@q AS first_q)`). Without one, BigQuery says "Generating an implicit alias for this PIVOT value is not supported".

## Where to pass them

In Querylab.io, a `-- querylab:param name TYPE = value` comment in the tab declares the parameter and its value, any type included, and every run and estimate sends it as a typed query parameter; an `@name` without a value is an error before the job is sent ([query parameters](https://docs.querylab.io/writing-sql/query-parameters/)). Elsewhere: `bq query --parameter`, the client libraries (`queryParameters` in the job configuration, with `parameterMode` set to `NAMED` or `POSITIONAL`), and the query settings pane in the Cloud console. The console supports only scalar types, not `ARRAY` and `STRUCT`, according to Google's page.

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:

- [Run parameterized queries](https://cloud.google.com/bigquery/docs/parameterized-queries)

- [Lexical structure, query parameters](https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#query_parameters)