The GoogleSQL procedural language lets you execute multiple statementsin one query as a multi-statement query. You can usea multi-statement query to:

• Run multiple statements in a sequence, with shared state.
• Automate management tasks such as creating or dropping tables.
• Implement complex logic using programming constructs such asIF andWHILE.

This reference contains the statements that are part of the GoogleSQLprocedural language. To learn more about how you can use thisprocedural language to write multi-statement queries, seeWork with multi-statement queries. To learn how youcan convert multi-statement queries into stored procedures, seeWork with stored procedures.

DECLARE

DECLAREvariable_name[,...][variable_type][DEFAULTexpression];

variable_name must be a valid identifier, andvariable_type is anyGoogleSQLtype.

Description

Declares a variable of the specified type. If theDEFAULT clause is specified,the variable is initialized with the value of the expression; if noDEFAULT clause is present, the variable is initialized with the valueNULL.

If[variable_type] is omitted then aDEFAULT clause must be specified. Thevariable’s type will be inferred by the type of the expression in theDEFAULTclause.

Variable declarations must appear before other procedural statements, or at thestart of aBEGIN block. Variable names are case-insensitive.

Multiple variable names can appear in a singleDECLARE statement, but onlyonevariable_type andexpression.

It's an error to declare a variable with the same name as a variabledeclared earlier in the current block or in a containing block.

If theDEFAULT clause is present, the value of the expression must becoercible to the specified type. The expression may reference other variablesdeclared previously within the same block or a containing block.

GoogleSQL also supportssystem variables. You don'tneed to declare system variables, but you can set any of them that aren'tmarked read-only. You can reference system variables in queries.

Examples

The following example initializes the variablex as anINT64 with the valueNULL.

DECLARExINT64;

The following example initializes the variabled as aDATE object with the value of the current date.

DECLAREdDATEDEFAULTCURRENT_DATE();

The following example initializes the variablesx,y, andz asINT64 with the value 0.

DECLAREx,y,zINT64DEFAULT0;

The following example declares a variable nameditem corresponding to anarbitrary item in theschema1.products table. The type ofitem is inferredfrom the table schema.

DECLAREitemDEFAULT(SELECTitemFROMschema1.productsLIMIT1);

SET

Syntax

SETvariable_name=expression;

SET(variable_name[,...])=(expression[,...]);

Description

Sets a variable to have the value of the provided expression, or sets multiplevariables at the same time based on the result of multiple expressions.

TheSET statement may appear anywhere within a multi-statement query.

Examples

The following example sets the variablex to have the value 5.

SETx=5;

The following example sets the variablea to have the value 4,b to have thevalue 'foo', and the variablec to have the valuefalse.

SET(a,b,c)=(1+3,'foo',false);

The following example assigns the result of a query to multiple variables.First, it declares two variables,target_word andcorpus_count; next, itassigns the results of aSELECT AS STRUCT queryto the two variables. The result of the query is a single row containing aSTRUCT with two fields; the first element isassigned to the first variable, and the second element is assigned to the secondvariable.

DECLAREtarget_wordSTRINGDEFAULT'methinks';DECLAREcorpus_count,word_countINT64;SET(corpus_count,word_count)=(SELECTASSTRUCTCOUNT(DISTINCTcorpus),SUM(word_count)FROMbigquery-public-data.samples.shakespeareWHERELOWER(word)=target_word);SELECTFORMAT('Found %d occurrences of "%s" across %d Shakespeare works',word_count,target_word,corpus_count)ASresult;

This statement list outputs the following string:

Found151occurrencesof"methinks"across38Shakespeareworks

EXECUTE IMMEDIATE

Syntax

EXECUTEIMMEDIATEsql_expression[INTOvariable[,...]][USINGidentifier[,...]];sql_expression:{"query_statement"|expression("query_statement")}identifier:{variable|value}[ASalias]

Description

Executes a dynamic SQL statement on the fly.

• sql_expression: An expression that can represent one of the following:

  • Aquery statement
  • An expression that you can use on a query statement
  • A singleDDL statement

  • A singleDML statement

  • A singleDCL statement

  This expression can't be a control statement likeIF.

• expression: Can be afunction,conditional expression, orexpression subquery.

• query_statement: Represents a valid standalone SQL statement to execute.If this returns a value, theINTO clause must contain values of the sametype. You may access both system variables and values present in theUSINGclause; all other local variables and query parameters aren't exposed tothe query statement.

• INTO clause: After the SQL expression is executed, you can store theresults in one or morevariables, using theINTO clause.

• USING clause: Before you execute your SQL expression, you can pass in oneor more identifiers from theUSING clause into the SQL expression.These identifiers function similarly to query parameters, exposing values tothe query statement. An identifier can be a variable or a value.

You can include these placeholders in thequery_statement for identifiersreferenced in theUSING clause:

• ?: The value for this placeholder is bound to an identifier in theUSINGclause by index.

  DECLAREyINT64;-- y = 1 * (3 + 2) = 5EXECUTEIMMEDIATE"SELECT ? * (? + 2)"INTOyUSING1,3;

• @identifier: The value for this placeholder is bound to an identifier intheUSING clause by name. This syntax is identical tothe query parameter syntax.

  DECLAREyINT64;-- y = 1 * (3 + 2) = 5EXECUTEIMMEDIATE"SELECT @a * (@b + 2)"INTOyUSING1asa,3asb;

Here are some additional notes about the behavior of theEXECUTE IMMEDIATEstatement:

• EXECUTE IMMEDIATE is restricted from being executed dynamically as anested element. This meansEXECUTE IMMEDIATE can't be nested in anotherEXECUTE IMMEDIATE statement.
• If anEXECUTE IMMEDIATE statement returns results, then those resultsbecome the result of the entire statement and any appropriatesystem variables are updated.
• The same variable can appear in both theINTO andUSING clauses.
• query_statement can contain a single parsed statement that contains otherstatements (for example, BEGIN...END)
• If zero rows are returned fromquery_statement, including from zero-rowvalue tables, all variables in theINTO clause are set to NULL.
• If one row is returned fromquery_statement, including from zero-rowvalue tables, values are assigned by position, not variable name.
• If anINTO clause is present, an error is thrown if you attempt to returnmore than one row fromquery_statement.

Examples

In this example, we create a table of books and populate it with data. Notethe different ways that you can reference variables, save values tovariables, and use expressions.

-- Create some variables.DECLAREbook_nameSTRINGDEFAULT'Ulysses';DECLAREbook_yearINT64DEFAULT1922;DECLAREfirst_dateINT64;-- Create a temporary table called Books.EXECUTEIMMEDIATE"CREATE TEMP TABLE Books (title STRING, publish_date INT64)";-- Add a row for Hamlet (less secure).EXECUTEIMMEDIATE"INSERT INTO Books (title, publish_date) VALUES('Hamlet', 1599)";-- Add a row for Ulysses, using the variables declared and the ? placeholder.EXECUTEIMMEDIATE"INSERT INTO Books (title, publish_date) VALUES(?, ?)"USINGbook_name,book_year;-- Add a row for Emma, using the identifier placeholder.EXECUTEIMMEDIATE"INSERT INTO Books (title, publish_date) VALUES(@name, @year)"USING1815asyear,"Emma"asname;-- Add a row for Middlemarch, using an expression.EXECUTEIMMEDIATECONCAT("INSERT INTO Books (title, publish_date)","VALUES('Middlemarch', 1871)");-- The table looks similar to the following:/*------------------+------------------+ | title            | publish_date     | +------------------+------------------+ | Hamlet           | 1599             | | Ulysses          | 1922             | | Emma             | 1815             | | Middlemarch      | 1871             | +------------------+------------------*/-- Save the publish date of the earliest book, Hamlet, to a variable called-- first_date.EXECUTEIMMEDIATE"SELECT MIN(publish_date) FROM Books LIMIT 1"INTOfirst_date;

BEGIN...END

Syntax

BEGINsql_statement_listEND;

Description

BEGIN initiates a block of statements where declared variables exist onlyuntil the correspondingEND.sql_statement_list is a list of zero or moreSQL statements ending with semicolons.

Variable declarations must appear at the start of the block, prior to othertypes of statements. Variables declared inside a block may only be referencedwithin that block and in any nested blocks. It's an error to declare a variablewith the same name as a variable declared in the same block or an outer block.

There is a maximum nesting level of 50 for blocks and conditional statementssuch asBEGIN/END,IF/ELSE/END IF, andWHILE/END WHILE.

BEGIN/END is restricted from being executed dynamically as a nested element.

You can use a label with this statement. To learn more, seeLabels.

Examples

The following example declares a variablex with the default value 10; then,it initiates a block, in which a variabley is assigned the value ofx,which is 10, and returns this value; next, theEND statement ends theblock, ending the scope of variabley; finally, it returns the value ofx.

DECLARExINT64DEFAULT10;BEGINDECLAREyINT64;SETy=x;SELECTy;END;SELECTx;

BEGIN...EXCEPTION...END

Syntax

BEGINsql_statement_listEXCEPTIONWHENERRORTHENsql_statement_listEND;

Description

BEGIN...EXCEPTION executes a block of statements. If any of the statementsencounter an error, the remainder of the block is skipped and the statements intheEXCEPTION clause are executed.

Within theEXCEPTION clause, you can access details about the error using thefollowingEXCEPTION system variables:

¹ The type for@@error.stack_trace isARRAY<STRUCT<line INT64, column INT64, filename STRING, location STRING>>.

As BigQuery reserves the right to revise error messages at any time,consumers of@@error.message shouldn't rely on error messages remaining thesame or following any particular pattern. Don't obtain error locationinformation by extracting text out of the error message — use@@error.stack_trace and@@error.statement_text instead.

To handle exceptions that are thrown (and not handled) by an exception handleritself, you must wrap the block in an outer block with a separate exceptionhandler.

The following shows how to use an outer block with a separate exception handler:

BEGINBEGIN...EXCEPTIONWHENERRORTHENSELECT1/0;END;EXCEPTIONWHENERRORTHEN-- The exception thrown from the inner exception handler lands here.END;

BEGIN...EXCEPTION blocks also supportDECLARE statements, just like anyotherBEGIN block. Variables declared in aBEGIN block are valid only intheBEGIN section, and may not be used in the block’s exception handler.

You can use a label with this statement. To learn more, seeLabels.

Examples

In this example, when the division by zero error occurs, instead ofstopping the entire multi-statement query, GoogleSQL stopsschema1.proc1() andschema1.proc2() and execute theSELECT statement inthe exception handler.

CREATEORREPLACEPROCEDUREschema1.proc1()BEGINSELECT1/0;END;CREATEORREPLACEPROCEDUREschema1.proc2()BEGINCALLschema1.proc1();END;BEGINCALLschema1.proc2();EXCEPTIONWHENERRORTHENSELECT@@error.message,@@error.stack_trace,@@error.statement_text,@@error.formatted_stack_trace;END;

When the exception handler runs, the variables will havethe following values:

CASE

Syntax

CASEWHENboolean_expressionTHENsql_statement_list[...][ELSEsql_statement_list]ENDCASE;

Description

Executes theTHEN sql_statement_list where the boolean expression is true,or the optionalELSE sql_statement_list if no conditions match.

CASE can have a maximum of 50 nesting levels.

CASE is restricted from being executed dynamically as a nested element. ThismeansCASE can't be nested in anEXECUTE IMMEDIATE statement.

Examples

In this example, a search if conducted for thetarget_product_ID in theproducts_a table. If the ID isn't found there, a search is conducted forthe ID in theproducts_b table. If the ID isn't found there, the statement intheELSE block is executed.

DECLAREtarget_product_idINT64DEFAULT103;CASEWHENEXISTS(SELECT1FROMschema.products_aWHEREproduct_id=target_product_id)THENSELECT'found product in products_a table';WHENEXISTS(SELECT1FROMschema.products_bWHEREproduct_id=target_product_id)THENSELECT'found product in products_b table';ELSESELECT'did not find product';ENDCASE;

CASE search_expression

Syntax

CASEsearch_expressionWHENexpressionTHENsql_statement_list[...][ELSEsql_statement_list]ENDCASE;

Description

Executes the firstsql_statement_list where the search expression is matchesaWHEN expression. Thesearch_expression is evaluated once and thentested against eachWHEN expression for equality until a match is found.If no match is found, then the optionalELSEsql_statement_listis executed.

CASE can have a maximum of 50 nesting levels.

CASE is restricted from being executed dynamically as a nested element. ThismeansCASE can't be nested in anEXECUTE IMMEDIATE statement.

Examples

The following example uses the product ID as the search expression. If theID is1,'Product one' is returned. If the ID is2,'Product two'is returned. If the ID is anything else,Invalid product is returned.

DECLAREproduct_idINT64DEFAULT1;CASEproduct_idWHEN1THENSELECTCONCAT('Product one');WHEN2THENSELECTCONCAT('Product two');ELSESELECTCONCAT('Invalid product');ENDCASE;

IF

Syntax

IFconditionTHEN[sql_statement_list][ELSEIFconditionTHENsql_statement_list][...][ELSEsql_statement_list]ENDIF;

Description

Executes the firstsql_statement_list where the condition is true, or theoptionalELSEsql_statement_list if no conditions match.

There is a maximum nesting level of 50 for blocks and conditional statementssuch asBEGIN/END,IF/ELSE/END IF, andWHILE/END WHILE.

IF is restricted from being executed dynamically as a nested element. ThismeansIF can't be nested in anEXECUTE IMMEDIATE statement.

Examples

The following example declares a INT64 variabletarget_product_id with a default value of 103; then, it checks whether thetableschema.products contains a row with theproduct_id column matchesthe value oftarget_product_id; if so, it outputs a string stating that theproduct has been found, along with the value ofdefault_product_id; if not,it outputs a string stating that the product hasn't been found, also with thevalue ofdefault_product_id.

DECLAREtarget_product_idINT64DEFAULT103;IFEXISTS(SELECT1FROMschema.productsWHEREproduct_id=target_product_id)THENSELECTCONCAT('found product ',CAST(target_product_idASSTRING));ELSEIFEXISTS(SELECT1FROMschema.more_productsWHEREproduct_id=target_product_id)THENSELECTCONCAT('found product from more_products table',CAST(target_product_idASSTRING));ELSESELECTCONCAT('did not find product ',CAST(target_product_idASSTRING));ENDIF;

Labels

Syntax

label_name:BEGINblock_statement_listEND[label_name];

label_name:LOOPloop_statement_listENDLOOP[label_name];

label_name:WHILEconditionDOloop_statement_listENDWHILE[label_name];

label_name:FORvariableINqueryDOloop_statement_listENDFOR[label_name];

label_name:REPEATloop_statement_listUNTILboolean_conditionENDREPEAT[label_name];

block_statement_list:{statement|break_statement_with_label}[,...]loop_statement_list:{statement|break_continue_statement_with_label}[,...]break_statement_with_label:{BREAK|LEAVE}label_name;break_continue_statement_with_label:{BREAK|LEAVE|CONTINUE|ITERATE}label_name;

Description

A BREAK or CONTINUE statement with a label provides an unconditional jump tothe end of the block or loop associated with that label. To use a label with ablock or loop, the label must appear at the beginning of the block or loop, andoptionally at the end.

• A label name may consist of any GoogleSQL identifier, including theuse of backticks to include reserved characters or keywords.

• Multipart path names can be used, but only as quoted identifiers.

  `foo.bar`: BEGIN ... END -- Worksfoo.bar: BEGIN ... END -- Doesn't work

• Label names are case-insensitive.

• Each stored procedure has an independent store of label names. For example,a procedure may redefine a label already used in a calling procedure.

• A loop or block may not repeat a label name used in an enclosing loop orblock.

• Repeated label names are allowed in non-overlapping parts inprocedural statements.

• A label and variable with the same name is allowed.

• When theBREAK,LEAVE,CONTINUE, orITERATE statement specifies alabel, it exits or continues the loop matching the label name, rather thanalways picking the innermost loop.

Examples

You can only reference a block or loop while inside of it.

label_1:BEGINSELECT1;BREAKlabel_1;SELECT2;-- UnreachedEND;

label_1:LOOPBREAKlabel_1;ENDLOOPlabel_1;WHILEx <1DOCONTINUElabel_1;-- ErrorENDWHILE;

Repeated label names are allowed in non-overlapping parts ofthe multi-statement query. This works:

label_1:BEGINBREAKlabel_1;END;label_2:BEGINBREAKlabel_2;END;label_1:BEGINBREAKlabel_1;END;

A loop or block may not repeat a label name used in an enclosing loop or block.This throws an error:

label_1:BEGINlabel_1:BEGIN-- ErrorBREAKlabel_1;END;END;

A label and variable can have same name. This works:

label_1:BEGINDECLARElabel_1INT64;BREAKlabel_1;END;

TheEND keyword terminating a block or loop may specify a label name, butthis is optional. These both work:

label_1:BEGINBREAKlabel_1;ENDlabel_1;

label_1:BEGINBREAKlabel_1;END;

You can't have a label at the end of a block or loop if there isn't a labelat the beginning of the block or loop. This throws an error:

BEGINBREAKlabel_1;ENDlabel_1;

In this example, theBREAK andCONTINUE statements target the outerlabel_1: LOOP, rather than the innerWHILE x < 1 DO loop:

label_1:LOOPWHILEx <1DOIFy <1THENCONTINUElabel_1;ELSEBREAKlabel_1;ENDWHILE;ENDLOOPlabel_1

ABREAK,LEAVE, orCONTINUE, orITERATE statement that specifies a labelthat doesn't exist throws an error:

WHILEx <1DOBREAKlabel_1;-- ErrorENDWHILE;

Exiting a block from within the exception handler section is allowed:

label_1:BEGINSELECT1;EXCEPTIONWHENERRORTHENBREAKlabel_1;SELECT2;-- UnreachedEND;

CONTINUE can't be used with a block label. This throws an error:

label_1:BEGINSELECT1;CONTINUElabel_1;-- ErrorSELECT2;END;

Loops

LOOP

Syntax

LOOPsql_statement_listENDLOOP;

Description

Executessql_statement_list until aBREAK orLEAVE statement exits theloop.sql_statement_list is a list of zero or more SQL statements ending withsemicolons.

LOOP is restricted from being executed dynamically as a nested element. ThismeansLOOP can't be nested in anEXECUTE IMMEDIATE statement.

You can use a label with this statement. To learn more, seeLabels.

Examples

The following example declares a variablex with the default value 0; then,it uses theLOOP statement to create a loop that executes until the variablex is greater than or equal to 10; after the loop exits, the exampleoutputs the value ofx.

DECLARExINT64DEFAULT0;LOOPSETx=x+1;IFx>=10THENLEAVE;ENDIF;ENDLOOP;SELECTx;

This example outputs the following:

/*----+ | x  | +----+ | 10 | +----*/

REPEAT

Syntax

REPEATsql_statement_listUNTILboolean_conditionENDREPEAT;

Description

Repeatedly executes a list of zero or more SQL statements until theboolean condition at the end of the list isTRUE. The boolean conditionmust be an expression. You can exit this loop early with theBREAK orLEAVEstatement.

REPEAT is restricted from being executed dynamically as a nested element. ThismeansREPEAT can't be nested in anEXECUTE IMMEDIATE statement.

You can use a label with this statement. To learn more, seeLabels.

Examples

The following example declares a variablex with the default value0; then,it uses theREPEAT statement to create a loop that executes until the variablex is greater than or equal to3.

DECLARExINT64DEFAULT0;REPEATSETx=x+1;SELECTx;UNTILx>=3ENDREPEAT;

This example outputs the following:

/*---+ | x | +---+ | 1 | +---*//*---+ | x | +---+ | 2 | +---*//*---+ | x | +---+ | 3 | +---*/

WHILE

Syntax

WHILEboolean_expressionDOsql_statement_listENDWHILE;

There is a maximum nesting level of 50 for blocks and conditional statementssuch asBEGIN/END,IF/ELSE/END IF, andWHILE/END WHILE.

Description

Whileboolean_expression is true, executessql_statement_list.boolean_expression is evaluated for each iteration of the loop.

WHILE is restricted from being executed dynamically as a nested element. ThismeansWHILE can't be nested in anEXECUTE IMMEDIATE statement.

You can use a label with this statement. To learn more, seeLabels.

BREAK

Syntax

BREAK;

Description

Exit the current loop.

It's an error to useBREAK outside of a loop.

You can use a label with this statement. To learn more, seeLabels.

Examples

The following example declares two variables,heads andheads_count; next,it initiates a loop, which assigns a random boolean value toheads, thenchecks to see whetherheads is true; if so, it outputs "Heads!" and incrementsheads_count; if not, it outputs "Tails!" and exits the loop; finally, itoutputs a string stating how many times the "coin flip" resulted in "heads."

DECLAREheadsBOOL;DECLAREheads_countINT64DEFAULT0;LOOPSETheads=RAND() <0.5;IFheadsTHENSELECT'Heads!';SETheads_count=heads_count+1;ELSESELECT'Tails!';BREAK;ENDIF;ENDLOOP;SELECTCONCAT(CAST(heads_countASSTRING),' heads in a row');

LEAVE

Synonym forBREAK.

CONTINUE

Syntax

CONTINUE;

Description

Skip any following statements in the current loop and return to the start ofthe loop.

It's an error to useCONTINUE outside of a loop.

You can use a label with this statement. To learn more, seeLabels.

Examples

The following example declares two variables,heads andheads_count; next,it initiates a loop, which assigns a random boolean value toheads, thenchecks to see whetherheads is true; if so, it outputs "Heads!", incrementsheads_count, and restarts the loop, skipping any remaining statements; if not,it outputs "Tails!" and exits the loop; finally, it outputs a string stating howmany times the "coin flip" resulted in "heads."

DECLAREheadsBOOL;DECLAREheads_countINT64DEFAULT0;LOOPSETheads=RAND() <0.5;IFheadsTHENSELECT'Heads!';SETheads_count=heads_count+1;CONTINUE;ENDIF;SELECT'Tails!';BREAK;ENDLOOP;SELECTCONCAT(CAST(heads_countASSTRING),' heads in a row');

ITERATE

Synonym forCONTINUE.

FOR...IN

Syntax

FORloop_variable_nameIN(table_expression)DOsql_expression_listENDFOR;

Description

Loops over every row intable_expression and assigns the row toloop_variable_name. Inside each loop, the SQL statements insql_expression_list are executed using the current value ofloop_variable_name.

The value oftable_expression is evaluated once at the start of the loop. Oneach iteration, the value ofloop_variable_name is aSTRUCT that containsthe top-level columns of the table expression as fields. The order in whichvalues are assigned toloop_variable_name isn't defined, unless the tableexpression has a top-levelORDER BY clause orUNNEST array operator.

The scope ofloop_variable_name is the body of the loop. The name ofloop_variable_name can't conflict with other variables within the samescope.

You can use a label with this statement. To learn more, seeLabels.

Example

FORrecordIN(SELECTword,word_countFROMbigquery-public-data.samples.shakespeareLIMIT5)DOSELECTrecord.word,record.word_count;ENDFOR;

Transactions

BEGIN TRANSACTION

Syntax

BEGIN[TRANSACTION];

Description

Begins a transaction.

The transaction ends when aCOMMIT TRANSACTION orROLLBACK TRANSACTION statement is reached. Ifexecution ends before reaching either of these statements,an automatic rollback occurs.

For more information about transactions in BigQuery, seeMulti-statement transactions.

Example

The following example performs a transaction that selects rows from anexisting table into a temporary table, deletes those rows from the originaltable, and merges the temporary table into another table.

BEGINTRANSACTION;-- Create a temporary table of new arrivals from warehouse #1CREATETEMPTABLEtmpASSELECT*FROMmyschema.NewArrivalsWHEREwarehouse='warehouse #1';-- Delete the matching records from the original table.DELETEmyschema.NewArrivalsWHEREwarehouse='warehouse #1';-- Merge the matching records into the Inventory table.MERGEmyschema.InventoryASIUSINGtmpASTONI.product=T.productWHENNOTMATCHEDTHENINSERT(product,quantity,supply_constrained)VALUES(product,quantity,false)WHENMATCHEDTHENUPDATESETquantity=I.quantity+T.quantity;DROPTABLEtmp;COMMITTRANSACTION;

COMMIT TRANSACTION

Syntax

COMMIT[TRANSACTION];

Description

Commits an open transaction. If no open transaction is in progress, then thestatement fails.

For more information about transactions in BigQuery, seeMulti-statement transactions.

Example

BEGINTRANSACTION;-- SQL statements for the transaction go here.COMMITTRANSACTION;

ROLLBACK TRANSACTION

Syntax

ROLLBACK[TRANSACTION];

Description

Rolls back an open transaction. If there is no open transaction in progress,then the statement fails.

For more information about transactions in BigQuery, seeMulti-statement transactions.

Example

The following example rolls back a transaction if an error occurs during thetransaction. To illustrate the logic, the example triggers a divide-by-zeroerror after inserting a row into a table. After these statements run, the tableis unaffected.

BEGINBEGINTRANSACTION;INSERTINTOmyschema.NewArrivalsVALUES('top load washer',100,'warehouse #1');-- Trigger an error.SELECT1/0;COMMITTRANSACTION;EXCEPTIONWHENERRORTHEN-- Roll back the transaction inside the exception handler.SELECT@@error.message;ROLLBACKTRANSACTION;END;

RAISE

Syntax

RAISE[USINGMESSAGE=message];

Description

Raises an error, optionally using the specified error message whenUSINGMESSAGE = message is supplied.

WhenUSING MESSAGE isn't supplied

TheRAISE statement must only be used within anEXCEPTION clause. TheRAISE statement will re-raise the exception that was caught, and preserve theoriginal stack trace.

WhenUSING MESSAGE is supplied

If theRAISE statement is contained within theBEGIN section of aBEGIN...EXCEPTION block:

• The handler will be invoked.

• The value of@@error.message will exactly match themessage stringsupplied (which may beNULL ifmessage isNULL).

• The stack trace will be set to theRAISE statement.

If theRAISE statement isn't contained within theBEGIN section of aBEGIN...EXCEPTION block, theRAISE statement stops execution of themulti-statement query with the error message supplied.

RETURN

RETURN stops execution of the multi-statements query.

CALL

Syntax

CALLprocedure_name(procedure_argument[,…])

Description

Calls aprocedure with an argument list.procedure_argument may be a variable or an expression.

ForOUT orINOUT arguments, a variable passed as an argument must have theproper GoogleSQLtype. The same variable may not appearmultiple times as anOUT orINOUT argument in the procedure's argument list.

The maximum depth of procedure calls is 50 frames.

CALL is restricted from being executed dynamically as a nested element. ThismeansCALL can't be nested in anEXECUTE IMMEDIATE statement.

Examples

The following example declares a variableretCode. Then, it calls theprocedureupdateSomeTables in the schemamySchema, passing the arguments'someAccountId' andretCode. Finally, it returns the value ofretCode.

DECLAREretCodeINT64;-- Procedure signature: (IN account_id STRING, OUT retCode INT64)CALLmySchema.UpdateSomeTables('someAccountId',retCode);SELECTretCode;