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

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

For more information, see the following topics:

Syntax¶

DECLARE { | | | | }; [ { | | | | }; ... ]

The following sections describe the syntax for each type of declaration in more detail:

Variable declaration syntax
Cursor declaration syntax
RESULTSET declaration syntax
Nested stored procedure declaration syntax
Exception declaration syntax

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.

Nested stored procedure declaration syntax¶

Use the following syntax to declare a nested stored procedure:

PROCEDURE ( [ ] [ , ... ] ) RETURNS { | TABLE ( [ [ , ... ] ] ) } AS

Where:

_nestedprocedurename_

The name of the nested stored procedure. The name must follow the naming rules for Object identifiers.

( [ _argname_ _argdatatype_ ] [ , ... ] )

Specifies the input arguments for the nested stored procedure.

For _argname_, specify the name of the input argument.
For _argdatatype_, specify a SQL data type.

RETURNS { _resultdatatype_ | TABLE ( [ _colname_ _coldatatype_ [ , ... ] ] ) }

Specifies the type of the result returned by the stored procedure. Currently, NOT NULL isn’t supported in the RETURNS parameter for nested stored procedures.

For RETURNS _resultdatatype_, specify a SQL data type.
For RETURNS TABLE ( [ _colname_ _coldatatype_ [ , ... ] ] ), if you know theSnowflake data types of the columns in the returned table, specify the column names and types:
RETURNS TABLE (sales_date DATE, quantity NUMBER)
Otherwise (for example, if you are determining the column types during run time), you can omit the column names and types:
Note
Currently, in the RETURNS TABLE(...) clause, you can’t specify GEOGRAPHY as a column type. This applies whether you are creating a stored or anonymous procedure.
CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
RETURNS TABLE(g GEOGRAPHY)
...
WITH test_return_geography_table_1() AS PROCEDURE
RETURNS TABLE(g GEOGRAPHY)
...
CALL test_return_geography_table_1();
If you attempt to specify GEOGRAPHY as a column type, calling the stored procedure results in the error:
Stored procedure execution error: data type of returned table does not match expected returned table type
To work around this issue, you can omit the column arguments and types in RETURNS TABLE().
CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
RETURNS TABLE()
...
WITH test_return_geography_table_1() AS PROCEDURE
RETURNS TABLE()
...
CALL test_return_geography_table_1();

AS _nestedproceduredefinition_

Defines the code executed by the nested stored procedure. The definition can consist of any valid code.

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 the following topics: