Scripting and stored procedures

Pipelines often need variables, conditional steps and dynamic SQL that a single query can't express.

A BigQuery script is several statements sent as one request, sharing variables and temporary tables. Each statement runs as a child job of the script job.

Building blocks:

DECLARE x INT64 DEFAULT 0; declares a variable and must come at the start of the script or of a BEGIN block. SET x = (SELECT COUNT(*) FROM t); assigns. SET (a, b) = (SELECT AS STRUCT ...) assigns several at once.
Control flow: IF ... THEN ... ELSEIF ... ELSE ... END IF, LOOP ... END LOOP with LEAVE/BREAK, WHILE ... DO ... END WHILE, REPEAT, and FOR row IN (SELECT ...) DO ... END FOR.
EXECUTE IMMEDIATE 'SQL with ?' USING value runs dynamic SQL, for example to loop over table names. INTO var captures a single-row result.
CREATE TEMP TABLE holds intermediate results for the rest of the script.
Errors: BEGIN ... EXCEPTION WHEN ERROR THEN ... END, with @@error.message describing the failure. RAISE USING MESSAGE = '...' raises your own error.
System variables such as @@row_count (rows affected by the last DML) and @@script.bytes_billed.

A stored procedure packages a script with parameters: CREATE PROCEDURE ds.proc(IN d DATE, OUT n INT64) BEGIN ... END; and CALL ds.proc('2025-06-01', n);. Procedures can call other procedures and run DML, DDL and transactions. BigQuery also supports Apache Spark stored procedures for PySpark logic.

Example

DECLARE run_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
DECLARE n INT64;

BEGIN
  CREATE TEMP TABLE daily AS
  SELECT user_id, COUNT(*) AS events
  FROM web.events
  WHERE DATE(event_ts) = run_date
  GROUP BY user_id;

  SET n = (SELECT COUNT(*) FROM daily);
  IF n = 0 THEN
    RAISE USING MESSAGE = FORMAT('No events for %t', run_date);
  END IF;

  EXECUTE IMMEDIATE FORMAT(
    'CREATE OR REPLACE TABLE web.daily_%s AS SELECT * FROM daily',
    FORMAT_DATE('%Y%m%d', run_date));
EXCEPTION WHEN ERROR THEN
  SELECT 'failed: ' || @@error.message AS status;
END;

Remember

DECLARE at the top of a block, SET to assign
IF, LOOP, WHILE and FOR ... IN (query) give procedural control
EXECUTE IMMEDIATE runs dynamic SQL; EXCEPTION blocks catch errors
Procedures package scripts with IN/OUT parameters

Watch out for

Declaring variables after other statements; DECLARE must come first in its block.

Check yourself

How can a script build and run a statement whose table name is computed at run time?

Official docs: docs.cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks