DECLARE (Snowflake Scripting)

Declares one or more Snowflake Scripting variables, cursors, RESULTSETs, or exceptions.

For more information on variables, cursors, RESULTSETs, and exceptions, see:

See also:

LET

Syntax

DECLARE
  { <variable_declaration> | <cursor_declaration> | <resultset_declaration> | <exception_declaration> };
  [{ <variable_declaration> | <cursor_declaration> | <resultset_declaration> | <exception_declaration> }; ... ]
Copy

The syntax for each type of declaration is described below in more detail.

Variable declaration syntax

Use the following syntax to declare a variable.

<variable_declaration> ::=
  <variable_name> [<type>] [ { DEFAULT | := } <expression>]
Copy

Where:

variable_name

The name of the variable. The name must follow the naming rules for Object identifiers.

type

A SQL data type.

DEFAULT expression or . := expression

Assigns the value of expression to the variable. If both type and expression are specified, the expression must evaluate to a data type that matches, or can be implicitly cast to, the specified type.

For example:

profit NUMBER(38, 2) := 0;
Copy

For a complete example, see Examples.

For more information about variables, see Working with variables.

Cursor declaration syntax

Use the following syntax to declare a cursor.

<cursor_declaration> ::=
  <cursor_name> CURSOR FOR <query>
Copy

Where:

cursor_name

The name to give the cursor. This can be any valid Snowflake identifier that is not already in use in this block. The identifier is used by other cursor-related commands, such as FETCH.

query

The query that defines the result set that the cursor iterates over.

This can be almost any valid SELECT statement.

For example:

c1 CURSOR FOR SELECT id, price FROM invoices;
Copy

For more information about cursors (including complete examples), see Working with cursors.

RESULTSET declaration syntax

Use the following syntax to declare a RESULTSET.

<resultset_name> RESULTSET [ { DEFAULT | := } [ ASYNC ] ( <query> ) ] ;
Copy

Where:

resultset_name

The name to give the RESULTSET.

The name should be unique within the current scope.

The name must follow the naming rules for Object identifiers.

ASYNC

Snowflake logo in black (no text) Preview Feature — Open

Available to all accounts.

Runs the query as an anonymous child job.

The query can be any valid SQL statement, including SELECT statements and DML statements, such as INSERT or UPDATE.

When this keyword is omitted, the stored procedure runs child jobs sequentially, and each child job waits for the running child job to finish before it starts.

You can use this keyword to run multiple child jobs concurrently, which can improve efficiency and reduce overall run time.

You can use AWAIT and CANCEL statements to manage asynchronous child jobs for a RESULTSET.

DEFAULT query or . := query

Assigns the value of query to the RESULTSET.

For example:

res RESULTSET DEFAULT (SELECT col1 FROM mytable ORDER BY col1);
Copy

For more information about RESULTSETs (including complete examples), see Working with RESULTSETs.

Exception declaration syntax

Use the following syntax to declare an exception.

<exception_name> EXCEPTION [ ( <exception_number> , '<exception_message>' ) ] ;
Copy

Where:

exception_name

The name to give to the exception.

exception_number

A number to uniquely identify the exception. The number must be an integer between -20000 and -20999. The number should not be used for any other exception that exists at the same time.

Default: -20000

exception_message

A message to describe the exception. The message must not contain any double quote characters.

Default: Empty string.

For example:

exception_could_not_create_table EXCEPTION (-20003, 'ERROR: Could not create table.');
Copy

For more information about exceptions (including complete examples), see Handling exceptions.

Examples

This example declares a variable named profit for use in a Snowflake Scripting anonymous block:

DECLARE
  profit number(38, 2) DEFAULT 0.0;
BEGIN
  LET cost number(38, 2) := 100.0;
  LET revenue number(38, 2) DEFAULT 110.0;

  profit := revenue - cost;
  RETURN profit;
END;
Copy

Note: If you are using SnowSQL, the Classic Console, or the execute_stream or execute_string method in Python Connector code, use this example instead (see Using Snowflake Scripting in SnowSQL, the Classic Console, and Python Connector):

EXECUTE IMMEDIATE 
$$
DECLARE
  profit number(38, 2) DEFAULT 0.0;
BEGIN
  LET cost number(38, 2) := 100.0;
  LET revenue number(38, 2) DEFAULT 110.0;

  profit := revenue - cost;
  RETURN profit;
END;
$$
;
Copy

For more examples that declare variables, cursors, RESULTSETs, and exceptions, see:

Language: English