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

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

To call the procedure, use the CALL statement:

CALL mydataset.create_customer();

Example:2 Procedure with output parameters

\n    CREATE PROCEDURE mydataset.SelectFromTablesAndAppend(\n      target_date DATE, OUT rows_added INT64)\n    BEGIN\n      CREATE TEMP TABLE DataForTargetDate AS\n      SELECT t1.id, t1.x, t2.y\n      FROM dataset.partitioned_table1 AS t1\n      JOIN dataset.partitioned_table2 AS t2\n      ON t1.id = t2.id\n      WHERE t1.date = target_date\n        AND t2.date = target_date;\n\n  SET rows_added = (SELECT COUNT(*) FROM DataForTargetDate);\n\n  SELECT id, x, y, target_date  -- note that target_date is a parameter\n  FROM DataForTargetDate;\n\n  DROP TABLE DataForTargetDate;\nEND;\n

Usage:

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

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]\n[[project_name.]dataset_name.]procedure_name (procedure_argument[, ...] )\n[OPTIONS(procedure_option_list)]\nBEGIN\nstatement_list\nEND;\n\nprocedure_argument: [procedure_argument_mode] argument_name argument_type\n\nprocedure_argument_mode: IN | OUT | INOUT