Procedures and table functions

A temporary function returns one value and lasts for one script. When a script needs several statements run together, or a query with parameters that other queries read, you store a procedure or a table function in a dataset.

How it works

CREATE PROCEDURE saves a block of statements under a name, and CALL runs the block. A procedure has no return value of its own. It hands values back through its OUT and INOUT arguments. CREATE TABLE FUNCTION saves a query under a name. You call it in a FROM, like a table whose rows depend on its arguments.

Common mistake

An OUT parameter does not carry the old value into the body. A procedure that adds to it, as in SET total = total + 1, adds to NULL and returns NULL, with no error.

Worked example

DECLARE first_day DATE;
DECLARE last_day DATE;

CREATE OR REPLACE PROCEDURE learn.mot.test_span(IN wanted_make STRING, OUT first_day DATE, OUT last_day DATE)
BEGIN
  SET (first_day, last_day) = (
    SELECT AS STRUCT MIN(completed_date), MAX(completed_date)
    FROM `learn.mot.fact_mot_test`
    WHERE make = wanted_make
      AND completed_date BETWEEN '2024-01-01' AND '2024-03-31'
  );
END;

CREATE OR REPLACE TABLE FUNCTION learn.mot.tests_by_day(wanted_make STRING)
RETURNS TABLE<completed_date DATE, tests INT64> AS (
  SELECT completed_date, COUNT(*) AS tests
  FROM `learn.mot.fact_mot_test`
  WHERE make = wanted_make
    AND completed_date BETWEEN '2024-01-01' AND '2024-03-31'
  GROUP BY completed_date
);

CALL learn.mot.test_span('HONDA', first_day, last_day);

SELECT completed_date, tests
FROM learn.mot.tests_by_day('HONDA')
WHERE completed_date IN (first_day, last_day)
ORDER BY completed_date;

The procedure test_span takes a make and gives back two dates through its OUT parameters: the first and the last day with a test in the first three months of 2024.

The table function tests_by_day takes a make and returns one row for each day, with the number of tests.

CALL fills the variables first_day and last_day. The call has no value of its own, so the dates come back only through the variables.

The last query reads the table function in its FROM and keeps the two days that the procedure found. The variables work in WHERE like any other value.

The procedure ran statements and handed back two values. The function handed back rows.

All BigQuery lessons