Procedural language

The GoogleSQL procedural language lets you execute multiple statements in one query as a multi-statement query. You can use a 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 as IF and WHILE.

This reference contains the statements that are part of the GoogleSQL procedural language. To learn more about how you can use this procedural language to write multi-statement queries, see Work with multi-statement queries. To learn how you can convert multi-statement queries into stored procedures, see Work with stored procedures.

`DECLARE`

DECLARE variable_name[, ...] [variable_type] [DEFAULT expression];

variable_name must be a valid identifier, and variable_type is any GoogleSQL type.

Description

Declares a variable of the specified type. If the DEFAULT clause is specified, the variable is initialized with the value of the expression; if no DEFAULT clause is present, the variable is initialized with the value NULL.

If [variable_type] is omitted then a DEFAULT clause must be specified. The variable’s type will be inferred by the type of the expression in the DEFAULT clause.

Variable declarations must appear before other procedural statements, or at the start of a BEGIN block. Variable names are case-insensitive.

Multiple variable names can appear in a single DECLARE statement, but only one variable_type and expression.

It is an error to declare a variable with the same name as a variable declared earlier in the current block or in a containing block.

If the DEFAULT clause is present, the value of the expression must be coercible to the specified type. The expression may reference other variables declared previously within the same block or a containing block.

GoogleSQL also supports system variables. You do not need to declare system variables, but you can set any of them that are not marked read-only. You can reference system variables in queries.

Examples

The following example initializes the variable x as an INT64 with the value NULL.

DECLARE x INT64;

The following example initializes the variable d as a DATE object with the value of the current date.

DECLARE d DATE DEFAULT CURRENT_DATE();

The following example initializes the variables x, y, and z as INT64 with the value 0.

DECLARE x, y, z INT64 DEFAULT 0;

The following example declares a variable named item corresponding to an arbitrary item in the schema1.products table. The type of item is inferred from the table schema.

DECLARE item DEFAULT (SELECT item FROM schema1.products LIMIT 1);

`SET`

Syntax

SET variable_name = expression;

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

Description

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

The SET statement may appear anywhere within a multi-statement query.

Examples

The following example sets the variable x to have the value 5.

SET x = 5;

The following example sets the variable a to have the value 4, b to have the value 'foo', and the variable c to have the value false.

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 and corpus_count; next, it assigns the results of a SELECT AS STRUCT query to the two variables. The result of the query is a single row containing a STRUCT with two fields; the first element is assigned to the first variable, and the second element is assigned to the second variable.

DECLARE target_word STRING DEFAULT 'methinks';
DECLARE corpus_count, word_count INT64;

SET (corpus_count, word_count) = (
  SELECT AS STRUCT COUNT(DISTINCT corpus), SUM(word_count)
  FROM bigquery-public-data.samples.shakespeare
  WHERE LOWER(word) = target_word
);

SELECT
  FORMAT('Found %d occurrences of "%s" across %d Shakespeare works',
         word_count, target_word, corpus_count) AS result;

This statement list outputs the following string:

Found 151 occurrences of "methinks" across 38 Shakespeare works

`EXECUTE IMMEDIATE`

Syntax

EXECUTE IMMEDIATE sql_expression [ INTO variable[, ...] ] [ USING identifier[, ...] ];

sql_expression:
  { "query_statement" | expression("query_statement") }

identifier:
  { variable | value } [ AS alias ]

Description

Executes a dynamic SQL statement on the fly.

sql_expression: An expression that can represent one of the following:
- A query statement
- An expression that you can use on a query statement
- A single DDL statement
- A single DML statement
- A single DCL statement
This expression cannot be a control statement like IF.
expression: Can be a function, conditional expression, or expression subquery.
query_statement: Represents a valid standalone SQL statement to execute. If this returns a value, the INTO clause must contain values of the same type. You may access both system variables and values present in the USING clause; all other local variables and query parameters are not exposed to the query statement.
INTO clause: After the SQL expression is executed, you can store the results in one or more variables, using the INTO clause.
USING clause: Before you execute your SQL expression, you can pass in one or more identifiers from the USING clause into the SQL expression. These identifiers function similarly to query parameters, exposing values to the query statement. An identifier can be a variable or a value.

You can include these placeholders in the query_statement for identifiers referenced in the USING clause:

?: The value for this placeholder is bound to an identifier in the USING clause by index.
```
-- y = 1 * (3 + 2) = 5
EXECUTE IMMEDIATE "SELECT ? * (? + 2)" INTO y USING 1, 3;
```
@identifier: The value for this placeholder is bound to an identifier in the USING clause by name. This syntax is identical to the query parameter syntax.
```
-- y = 1 * (3 + 2) = 5
EXECUTE IMMEDIATE "SELECT @a * (@b + 2)" INTO y USING 1 as a, 3 as b;
```

Here are some additional notes about the behavior of the EXECUTE IMMEDIATE statement:

EXECUTE IMMEDIATE is restricted from being executed dynamically as a nested element. This means EXECUTE IMMEDIATE cannot be nested in another EXECUTE IMMEDIATE statement.
If an EXECUTE IMMEDIATE statement returns results, then those results become the result of the entire statement and any appropriate system variables are updated.
The same variable can appear in both the INTO and USING clauses.
query_statement can contain a single parsed statement that contains other statements (for example, BEGIN...END)
If zero rows are returned from query_statement, including from zero-row value tables, all variables in the INTO clause are set to NULL.
If one row is returned from query_statement, including from zero-row value tables, values are assigned by position, not variable name.
If an INTO clause is present, an error is thrown if you attempt to return more than one row from query_statement.

Examples

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

-- create some variables
DECLARE book_name STRING DEFAULT 'Ulysses';
DECLARE book_year INT64 DEFAULT 1922;
DECLARE first_date INT64;

-- Create a temporary table called Books.
EXECUTE IMMEDIATE
  "CREATE TEMP TABLE Books (title STRING, publish_date INT64)";

-- Add a row for Hamlet (less secure)
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES('Hamlet', 1599)";

-- add a row for Ulysses, using the variables declared and the ? placeholder
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES(?, ?)"
  USING book_name, book_year;

-- add a row for Emma, using the identifier placeholder
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES(@name, @year)"
  USING 1815 as year, "Emma" as name;

-- add a row for Middlemarch, using an expression
EXECUTE IMMEDIATE
  CONCAT(
    "INSERT INTO Books (title, publish_date)", "VALUES('Middlemarch', 1871)"
  );

-- save the publish date of the first book, Hamlet, to a variable called
-- first_date
EXECUTE IMMEDIATE "SELECT publish_date FROM Books LIMIT 1" INTO first_date;

/*------------------+------------------*
 | title            | publish_date     |
 +------------------+------------------+
 | Hamlet           | 1599             |
 | Ulysses          | 1922             |
 | Emma             | 1815             |
 | Middlemarch      | 1871             |
 *------------------+------------------*/

`BEGIN...END`

Syntax

BEGIN
  sql_statement_list
END;

Description

BEGIN initiates a block of statements where declared variables exist only until the corresponding END. sql_statement_list is a list of zero or more SQL statements ending with semicolons.

Variable declarations must appear at the start of the block, prior to other types of statements. Variables declared inside a block may only be referenced within that block and in any nested blocks. It is an error to declare a variable with 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 statements such as BEGIN/END, IF/ELSE/END IF, and WHILE/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, see Labels.

Examples

The following example declares a variable x with the default value 10; then, it initiates a block, in which a variable y is assigned the value of x, which is 10, and returns this value; next, the END statement ends the block, ending the scope of variable y; finally, it returns the value of x.

DECLARE x INT64 DEFAULT 10;
BEGIN
  DECLARE y INT64;
  SET y = x;
  SELECT y;
END;
SELECT x;

`BEGIN...EXCEPTION...END`

Syntax

BEGIN
  sql_statement_list
EXCEPTION WHEN ERROR THEN
  sql_statement_list
END;

Description

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

Within the EXCEPTION clause, you can access details about the error using the following EXCEPTION system variables:

Name	Type	Description
`@@error.formatted_stack_trace`	`STRING`	The content of `@@error.stack_trace` expressed as a human readable string. This value is intended for display purposes, and is subject to change without notice. Programmatic access to an error's stack trace should use `@@error.stack_trace` instead.
`@@error.message`	`STRING`	Specifies a human-readable error message.
`@@error.stack_trace`	See 1.	Each element of the array corresponds to a statement or procedure call executing at the time of the error, with the currently executing stack frame appearing first. The meaning of each field is defined as follows: line/column: Specifies the line and column number of the stack frame, starting with 1. If the frame occurs within a procedure body, then `line 1 column 1` corresponds to the `BEGIN` keyword at the start of the procedure body. location: If the frame occurs within a procedure body, specifies the full name of the procedure, in the form `[project_name].[schema_name].[procedure_name]`. If the frame refers to a location in a top-level multi-statement query, this field is `NULL`. filename: Reserved for future use. Always `NULL`.
`@@error.statement_text`	`STRING`	Specifies the text of the statement which caused the error.

¹ The type for @@error.stack_trace is ARRAY<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 should not rely on error messages remaining the same or following any particular pattern. Do not obtain error location information 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 handler itself, you must wrap the block in an outer block with a separate exception handler.

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

BEGIN
  BEGIN
    ...
  EXCEPTION WHEN ERROR THEN
    SELECT 1/0;
  END;
EXCEPTION WHEN ERROR THEN
  -- The exception thrown from the inner exception handler lands here.
END;

BEGIN...EXCEPTION blocks also support DECLARE statements, just like any other BEGIN block. Variables declared in a BEGIN block are valid only in the BEGIN section, and may not be used in the block’s exception handler.

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

Examples

In this example, when the division by zero error occurs, instead of stopping the entire multi-statement query, GoogleSQL will stop schema1.proc1() and schema1.proc2() and execute the SELECT statement in the exception handler.

CREATE OR REPLACE PROCEDURE schema1.proc1() BEGIN
  SELECT 1/0;
END;

CREATE OR REPLACE PROCEDURE schema1.proc2() BEGIN
  CALL schema1.proc1();
END;

BEGIN
  CALL schema1.proc2();
EXCEPTION WHEN ERROR THEN
  SELECT
    @@error.message,
    @@error.stack_trace,
    @@error.statement_text,
    @@error.formatted_stack_trace;
END;

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

Variable	Value
`@@error.message`	`"Query error: division by zero: 1 / 0 at <project>.schema1.proc1:2:3]"`
`@@error.stack_trace`	`[` `STRUCT(2 AS line, 3 AS column, NULL AS filename, "<project>.schema1.proc1:2:3" AS location),` `STRUCT(2 AS line, 3 AS column, NULL AS filename, "<project>.schema1.proc2:2:3" AS location),` `STRUCT(10 AS line, 3 AS column, NULL AS filename, NULL AS location),` `]`
`@@error.statement_text`	`"SELECT 1/0"`
`@@error.formatted_stack_trace`	`"At <project>.schema1.proc1[2:3]\nAt <project>.schema1.proc2[2:3]\nAt [10:3]"`

`CASE`

Syntax

CASE
  WHEN boolean_expression THEN sql_statement_list
  [...]
  [ELSE sql_statement_list]
END CASE;

Description

Executes the THEN sql_statement_list where the boolean expression is true, or the optional ELSE 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. This means CASE cannot be nested in an EXECUTE IMMEDIATE statement.

Examples

In this example, a search if conducted for the target_product_ID in the products_a table. If the ID is not found there, a search is conducted for the ID in the products_b table. If the ID is not found there, the statement in the ELSE block is executed.

DECLARE target_product_id INT64 DEFAULT 103;
CASE
  WHEN
    EXISTS(SELECT 1 FROM schema.products_a WHERE product_id = target_product_id)
    THEN SELECT 'found product in products_a table';
  WHEN
    EXISTS(SELECT 1 FROM schema.products_b WHERE product_id = target_product_id)
    THEN SELECT 'found product in products_b table';
  ELSE
    SELECT 'did not find product';
END CASE;

`CASE search_expression`

Syntax

CASE search_expression
  WHEN expression THEN sql_statement_list
  [...]
  [ELSE sql_statement_list]
END CASE;

Description

Executes the first sql_statement_list where the search expression is matches a WHEN expression. The search_expression is evaluated once and then tested against each WHEN expression for equality until a match is found. If no match is found, then the optional ELSE sql_statement_list is executed.

CASE can have