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.

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

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

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:

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

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:

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:

SELECT *
FROM @table_name

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:

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:

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

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

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