Stored procedures

Opening your lesson

A stored procedure saves a script under a dataset name. Creating the procedure stores its definition; CALL runs it. A report table can still contain yesterday's rows after its procedure definition changes. Save the definition, then invoke it, then inspect the table it produced.

Use CREATE OR REPLACE TABLE inside a rebuild procedure when rerunning it should produce the same complete report. An append needs a different design, such as a keyed MERGE, or repeated runs can duplicate rows. This console supports procedures without parameters and straight-line SQL bodies. BigQuery itself also supports parameters, control flow and authorized routines. These examples keep the scope narrow: a procedure rebuilds a sandbox result, and a final query verifies the rows. It doesn't create a schedule or grant anyone access.

CREATE OR REPLACE PROCEDURE practice.refresh_counts()
BEGIN
  CREATE OR REPLACE TABLE practice.channel_counts AS
  SELECT channel, COUNT(*) AS orders FROM shop.orders GROUP BY channel;
END;
CALL practice.refresh_counts();
SELECT * FROM practice.channel_counts;

CALL executes the saved body. The last query reads its output.

Choose your character

Your Work Gym account