Parameters in BigQuery

You can use query parameters in BigQuery SQL (now in the console as well!) — but how are they different from variables, and when should you use each?

Both parameters and variables act as placeholders and have a defined data type. The difference is where their value comes from and how they’re used.

Parameters (like @corpus)
👉 Are not computed inside the query
👉 Are passed from the outside (Python, UI, API, etc.)

Variables (DECLARE, SET)
👉 Are defined and computed inside a SQL script or stored procedure
👉 Let you store a value and reuse it later in the same script

So what’s the real difference?
➡️ Variables are essential for Dynamic SQL (EXECUTE IMMEDIATE)
➡️ Parameters can filter data, but cannot control identifiers (e.g. table or column names)

🚨 Security
When values come from user input or external sources, parameters are the safer choice—they reduce the risk of SQL injection.

🚅 Performance
Parameters may allow the optimizer to reuse execution plans, while variables can sometimes prevent that.

SELECT
    word,
    word_count
  FROM
    `bigquery-public-data.samples.shakespeare`
  WHERE
    corpus = @corpus

  ORDER BY
    word_count DESC;

DECLARE corpus_var STRING DEFAULT 'sonnets';

SELECT
    word,
    word_count
  FROM
    `bigquery-public-data.samples.shakespeare`
  WHERE
    corpus = corpus_var

  ORDER BY
    word_count DESC;