# Stored procedures in BigQuery

> A BigQuery stored procedure is a saved script you run with CALL. It takes IN, OUT and INOUT arguments, OUT arguments must be variables, temp tables it creates stay visible to the caller, and its name is case-sensitive.

Source: https://docs.querylab.io/bigquery/stored-procedures/

Updated: 2026-10-14

A stored procedure is a [script](https://docs.querylab.io/bigquery/scripting/) saved in a dataset under a name, with arguments. You run it with `CALL`. Everything a script can do, a procedure can do: variables, loops, DML, DDL, temp tables, transactions and dynamic SQL. Procedures are how you package a multi-step job, such as a nightly rebuild or a data-quality check, so that schedulers and people run the same code.

## Creating and calling one

```sql
CREATE OR REPLACE PROCEDURE mydataset.count_big_orders(min_amount INT64, OUT n INT64)
BEGIN
  CREATE TEMP TABLE big_orders AS
  SELECT * FROM mydataset.orders WHERE amount >= min_amount;
  SET n = (SELECT COUNT(*) FROM big_orders);
END;
```

```sql
DECLARE n INT64;
CALL mydataset.count_big_orders(50, n);
SELECT n;
```

In Querylab.io, a dataset's SQL Templates menu has a Procedure skeleton to start from ([DDL operations](https://docs.querylab.io/schema-browser/ddl-operations/#dataset-templates)), and a procedure in the schema tree opens a tab with its Details and its defining SQL under Query ([Table information](https://docs.querylab.io/schema-browser/table-information/)).

Arguments are `IN` (the default; a value goes in), `OUT` (the procedure sets it) or `INOUT` (both). The body is between `BEGIN` and `END`, and the `DECLARE` rules of scripts apply inside it.

## OUT arguments must be variables

An `OUT` or `INOUT` argument needs somewhere to write, so you must pass a declared variable. Passing a literal or `NULL` fails with "... must be a variable". That also means a procedure with an `OUT` argument can't be called as a single statement: declare the variable first, which makes it a script.

## Temp tables stay visible after CALL

A temporary table created inside a procedure belongs to the calling script, not to the procedure. After `CALL mydataset.count_big_orders(50, n)`, the caller can run `SELECT * FROM big_orders`. We checked this: the rows the procedure selected were there.

That is useful for returning a whole result set, since a procedure has no `RETURNS`. It also means two procedures that create a temp table with the same name in one script collide. Use `CREATE OR REPLACE TEMP TABLE`, or distinct names.

## Errors

A `RAISE` or a failed statement inside a procedure stops it and passes the error to the caller. An `EXCEPTION` block around the `CALL` catches it, and `@@error.message` is the original message:

```sql
BEGIN
  CALL mydataset.check_orders();
EXCEPTION WHEN ERROR THEN
  SELECT @@error.message;  -- 'too big', the message the procedure raised
END;
```

Uncaught, the error names the procedure and the line and column inside it, such as `... at [mydataset.check_orders:1:21]`.

## Names and permissions

- Procedure names are case-sensitive. `CALL mydataset.COUNT_BIG_ORDERS(...)` fails with "Not found" when the procedure is `count_big_orders`.
- A caller needs `bigquery.routines.get` on the procedure and access to everything the procedure reads and writes, unless it is an [authorized routine](https://docs.querylab.io/bigquery/authorized-views/).

## What a CALL costs

A `CALL` is a script job, and each statement inside the procedure is billed like it would be in a script: bytes per statement, added up. There is no extra charge for calling a procedure. See [Scripting](https://docs.querylab.io/bigquery/scripting/#how-a-script-runs-and-is-billed).

## Procedures in other languages

Besides SQL, BigQuery can run stored procedures for Apache Spark, written in Python, Java or Scala. They run on Spark through a connection and are billed for that compute. Use them for work that needs Spark libraries. Everything else is simpler as a SQL procedure.

## Procedure or table function

A procedure can change data and run many statements, but it can't be used inside a query. A [table function](https://docs.querylab.io/bigquery/table-functions/) is a single `SELECT` you call in `FROM`. Use a procedure for jobs, a table function for reusable queries.

Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Work with SQL stored procedures](https://cloud.google.com/bigquery/docs/procedures)

- [DDL, CREATE PROCEDURE](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_procedure)

- [Procedural language, CALL](https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#call)