Work with SQL stored procedures

Astored procedure is a collection of statements that can be called from otherqueries or other stored procedures. A procedure can take input arguments andreturn values as output. You name and store a procedure in aBigQuery dataset. A stored procedure can access or modify dataacross multiple datasets by multiple users. It can also contain amulti-statement query.

Some stored procedures are built into BigQuery and don't need tobe created. These are calledsystem procedures and you can learn more aboutthem in theSystem procedures reference.

Stored procedures supportprocedural language statements, which let you dothings like define variables and implement control flow. You can learn moreabout procedural language statements in theProcedural language reference.

Create a stored procedure

To create a procedure, use theCREATE PROCEDUREstatement.

In the following conceptual example,procedure_name representsthe procedure and the body of the procedure appears betweenBEGIN andEND statements:

CREATEPROCEDUREdataset_name.procedure_name()BEGIN-- statements hereEND

The following example shows a procedure that contains a multi-statement query.The multi-statement query sets a variable, runs anINSERT statement, anddisplays the result as a formatted text string.

CREATEORREPLACEPROCEDUREmydataset.create_customer()BEGINDECLAREidSTRING;SETid=GENERATE_UUID();INSERTINTOmydataset.customers(customer_id)VALUES(id);SELECTFORMAT("Created customer %s",id);END

In the preceding example, the name of the procedure ismydataset.create_customer, and the body of procedure appears betweenBEGIN andEND statements.

To call the procedure, use theCALLstatement:

CALLmydataset.create_customer();

Pass a value in with an input parameter

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

CREATEORREPLACEPROCEDUREmydataset.create_customer(nameSTRING)BEGINDECLAREidSTRING;SETid=GENERATE_UUID();INSERTINTOmydataset.customers(customer_id,name)VALUES(id,name);SELECTFORMAT("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 valuefrom the procedure, but does not allow input for the procedure. To create anoutput parameter, use theOUT keyword before the name of the parameter.

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

CREATEORREPLACEPROCEDUREmydataset.create_customer(nameSTRING,OUTidSTRING)BEGINSETid=GENERATE_UUID();INSERTINTOmydataset.customers(customer_id,name)VALUES(id,name);SELECTFORMAT("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.DECLAREidSTRING;CALLmydataset.create_customer("alice",id);--- Display the record.SELECT*FROMmydataset.customersWHEREcustomer_id=id;

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

A procedure can also have input/output parameters. An input/output parameterreturns a value from the procedure and also accepts input for the procedure. Tocreate an input/output parameter, use theINOUT keyword before the name of theparameter. For more information, seeArgument mode.

Authorize routines

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

Authorized routines cancreate,drop,andmanipulate tables,as well asinvoke other stored procedureson the underlying table.

For more information, seeAuthorized routines.

Call a stored procedure

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

CALLmydataset.create_customer();

CALLBQ.REFRESH_MATERIALIZED_VIEW;