DECLARE (Snowflake Scripting) | Snowflake Documentation (original) (raw)

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 { | | | }; [{ | | | }; ... ]

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

Variable declaration syntax

Use the following syntax to declare a variable.

::= [] [ { DEFAULT | := } ]

Where:

_variablename_

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;

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 FOR

Where:

_cursorname_

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

_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;

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

RESULTSET declaration syntax

Use the following syntax to declare a RESULTSET.

RESULTSET [ { DEFAULT | := } [ ASYNC ] ( ) ] ;

Where:

_resultsetname_

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

Runs the query as an asynchronous 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 CANCELstatements 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);

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

Exception declaration syntax

Use the following syntax to declare an exception.

EXCEPTION [ ( , '' ) ] ;

Where:

_exceptionname_

The name to give to the exception.

_exceptionnumber_

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

_exceptionmessage_

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.');

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;

Note: If you use Snowflake CLI, SnowSQL, the Classic Console, or theexecute_stream or execute_string method in Python Connectorcode, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, the Classic Console, and Python Connector):

EXECUTE IMMEDIATE DECLAREprofitnumber(38,2)DEFAULT0.0;BEGINLETcostnumber(38,2):=100.0;LETrevenuenumber(38,2)DEFAULT110.0;profit:=revenue−cost;RETURNprofit;END;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;DECLAREprofitnumber(38,2)DEFAULT0.0;BEGINLETcostnumber(38,2):=100.0;LETrevenuenumber(38,2)DEFAULT110.0;profit:=revenue−cost;RETURNprofit;END; ;

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