BigQuery Create stored procedure

A stored procedure is a collection of SQL statements that you can execute in one request. Stored procedures can use variables and control-flow statements

Create Stored Procedure example

    CREATE OR REPLACE PROCEDURE mydataset.create_customer()
    BEGIN
      DECLARE id STRING;
      SET id = GENERATE_UUID();
      INSERT INTO mydataset.customers (customer_id)
        VALUES(id);
      SELECT FORMAT("Created customer %s", id);
    END

To call the procedure, use the CALL statement:

CALL mydataset.create_customer();

Example:2 Procedure with output parameters

    CREATE PROCEDURE mydataset.SelectFromTablesAndAppend(
      target_date DATE, OUT rows_added INT64)
    BEGIN
      CREATE TEMP TABLE DataForTargetDate AS
      SELECT t1.id, t1.x, t2.y
      FROM dataset.partitioned_table1 AS t1
      JOIN dataset.partitioned_table2 AS t2
      ON t1.id = t2.id
      WHERE t1.date = target_date
        AND t2.date = target_date;

  SET rows_added = (SELECT COUNT(*) FROM DataForTargetDate);

  SELECT id, x, y, target_date  -- note that target_date is a parameter
  FROM DataForTargetDate;

  DROP TABLE DataForTargetDate;
END;

Usage:

    DECLARE rows_added INT64;
    CALL mydataset.SelectFromTablesAndAppend(CURRENT_DATE(), rows_added);
    SELECT FORMAT('Added %d rows', rows_added);

Explanation

Stored procedures can accept input parameters and return output parameters, you can use variables, temp tables inside a procedure. DDL and DML statements are also allowed.

Syntax reference

CREATE [OR REPLACE] PROCEDURE [IF NOT EXISTS]
[[project_name.]dataset_name.]procedure_name (procedure_argument[, ...] )
[OPTIONS(procedure_option_list)]
BEGIN
statement_list
END;

procedure_argument: [procedure_argument_mode] argument_name argument_type

procedure_argument_mode: IN | OUT | INOUT