Learn Bigquery
Introduction
Architecture
Architecture Compute Storage
Unique Features
Enncryption Column Level Security Time Travel Caching Compression
Database Management
Create Table Create View Create Procedure
Best Practices
Execution plans
Interview Questions
Top Questions
26 Jan 21 · npack · #Bigquery ·   Bookmark  

Create Procedure in Google BigQuery - Syntax and 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