Work with SQL stored procedures

A stored procedure is a collection of statements that can be called from other queries or other stored procedures. A procedure can take input arguments and return values as output. You name and store a procedure in a BigQuery dataset. A stored procedure can access or modify data across multiple datasets by multiple users. It can also contain a multi-statement query.

Some stored procedures are built into BigQuery and don't need to be created. These are called system procedures and you can learn more about them in the System procedures reference.

Stored procedures support procedural language statements, which let you do things like define variables and implement control flow. You can learn more about procedural language statements in the Procedural language reference.

Create a stored procedure

To create a procedure, use the CREATE PROCEDURE statement.

In the following conceptual example, procedure_name represents the procedure and the body of the procedure appears between BEGIN and END statements:

CREATE PROCEDURE dataset_name.procedure_name()
BEGIN
-- statements here
END

The following example shows a procedure that contains a multi-statement query. The multi-statement query sets a variable, runs an INSERT statement, and displays the result as a formatted text string.

CREATE OR REPLACE PROCEDURE mydataset.create_customer()
BEGIN
  DECLARE id STRING;
  SET id = GENERATE_UUID();
  INSERT INTO mydataset.customers (customer_id)
    VALUES(id);
  SELECT FORMAT("Created customer %s", id);
END

In the preceding example, the name of the procedure is mydataset.create_customer, and the body of procedure appears between BEGIN and END statements.

To call the procedure, use the CALL statement:

CALL mydataset.create_customer();

Pass a value in with an input parameter

A procedure can have input parameters. An input parameter allows input for a procedure, but does not allow output.

CREATE OR REPLACE PROCEDURE mydataset.create_customer(name STRING)
BEGIN
DECLARE id STRING;
SET id = GENERATE_UUID();
INSERT INTO mydataset.customers (customer_id, name)
  VALUES(id, name);
SELECT FORMAT("Created customer %s (%s)", id, name);
END

Pass a value out with an output parameter

A procedure can have output parameters. An output parameter returns a value from the procedure, but does not allow input for the procedure. To create an output parameter, use the OUT keyword before the name of the parameter.

For example, this version of the procedure returns the new customer ID through the id parameter:

CREATE OR REPLACE PROCEDURE mydataset.create_customer(name STRING, OUT id STRING)
BEGIN
SET id = GENERATE_UUID();
INSERT INTO mydataset.customers (customer_id, name)
  VALUES(id, name);
SELECT FORMAT("Created customer %s (%s)", id, name);
END

To call this procedure, you must use a variable to receive the output value:

--- Create a new customer record.
DECLARE id STRING;
CALL mydataset.create_customer("alice",id);

--- Display the record.
SELECT * FROM mydataset.customers
WHERE customer_id = id;

Pass a value in and out with an input/output parameter

A procedure can also have input/output parameters. An input/output parameter returns a value from the procedure and also accepts input for the procedure. To create an input/output parameter, use the INOUT keyword before the name of the parameter. For more information, see Argument mode.

Authorize routines

You can authorize stored procedures as routines. Authorized routines let you share query results with specific users or groups without giving them access to the underlying tables that generated the results. For example, an authorized routine can compute an aggregation over data or look up a table value and use that value in a computation.

Authorized routines can create, drop, and manipulate tables, as well as invoke other stored procedures on the underlying table.

For more information, see Authorized routines.

Call a stored procedure

To call a stored procedure after it's been created, use the CALL statement. For example, the following statement calls the stored procedure create_customer:

CALL mydataset.create_customer();

Note: Calling a stored procedure rather than including the procedure's SQL statements directly in your query introduces a small performance overhead.

Call a system procedure

To call a built-in system procedure, use the CALL statement. For example, the following statement calls the system procedure BQ.REFRESH_MATERIALIZED_VIEW:

CALL BQ.REFRESH_MATERIALIZED_VIEW;