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
In the following conceptual example,
the procedure and the body of the procedure appears between
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
To call the procedure, use the
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
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
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.
For more information, see Authorized routines.
Call a stored procedure
To call a stored procedure after it's been created, use the
For example, the following statement calls the stored procedure
Call a system procedure
To call a built-in system procedure, use the
For example, the following statement calls the system procedure