BigQuery Create Procedure - Examples

Simple Stored Procedure

CREATE OR REPLACE PROCEDURE Dataset.Procedurename()
BEGIN
insert into dataset.tablename
select * from dataset.table2;
END

Stored Procedure with Input Parameter

CREATE OR REPLACE PROCEDURE Dataset.Procedurename(DLY_DT DATE)
BEGIN
insert into dataset.tablename
select * from dataset.table2 where File_date = DLY_DT;
END

Stored Procedure that uses Temp table

CREATE OR REPLACE PROCEDURE Dataset.Procedurename()
BEGIN
CREATE TEMP TABLE IF NOT EXISTS TEMP1
(
ID INT64
);

INSERT INTO TEMP1
SELECT ID FROM EMPLOYEE;

insert into dataset.tablename
select * from TEMP1;
END

Stored Procedure with variables

CREATE OR REPLACE PROCEDURE Dataset.Procedurename()
BEGIN
DECLARE V_DT DATE;
SET V_DT = (SELECT MAX(DT) FROM DATASET.TABLENAME);

insert into dataset.tablename
select * from dataset.table2 where File_date = V_DT;
END