26 Jan 21 ·  mCollins in Bigquery ·        Bookmark ·  Report ·  More actions.. Lock comments ·  Pin thread

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