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 
 (); 
 

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 
 ;