DECLARE (Snowflake Scripting)¶

Variable declaration syntax¶

Use the following syntax to declare a variable:

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

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>

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> ) ] ;

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

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

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:

<nested_procedure_name> PROCEDURE (
    [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> | TABLE ( [ <col_name> <col_data_type> [ , ... ] ] ) }
  AS <nested_procedure_definition>

Where:

nested_procedure_name

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

( [ arg_name arg_data_type ] [ , ... ] )

Specifies the input arguments for the nested stored procedure.

• For arg_name, specify the name of the input argument.

• For arg_data_type, specify a SQL data type.

RETURNS { result_data_type | TABLE ( [ col_name col_data_type [ , ... ] ] ) }

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 result_data_type, specify a SQL data type.

• For RETURNS TABLE ( [ col_name col_data_type [ , ... ] ] ), if you know the Snowflake data types of the columns in the returned table, specify the column names and types:

  RETURNS TABLE (sales_date DATE, quantity NUMBER)
  

  Copy

  Otherwise (for example, if you are determining the column types during run time), you can omit the column names and types:

  RETURNS TABLE ()
  

  Copy

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

  Copy

  WITH test_return_geography_table_1() AS PROCEDURE
    RETURNS TABLE(g GEOGRAPHY)
    ...
  CALL test_return_geography_table_1();
  

  Copy

  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
  

  Copy

  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()
    ...
  

  Copy

  WITH test_return_geography_table_1() AS PROCEDURE
    RETURNS TABLE()
    ...
  CALL test_return_geography_table_1();
  

  Copy

AS nested_procedure_definition

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_name> EXCEPTION [ ( <exception_number> , '<exception_message>' ) ] ;

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.