CALL (with anonymous procedure)

Creates and calls an anonymous procedure that is like a stored procedure but is not stored for later use.

With this command, you both create an anonymous procedure defined by parameters in the WITH clause and call that procedure.

You need not have a role with CREATE PROCEDURE schema privileges for this command.

The procedure runs with caller’s rights, which means that the procedure runs with the privileges of the caller, uses the current session context, and has access to the caller’s session variables and parameters.

See also:

CREATE PROCEDURE , CALL.

Syntax

Java and Scala

WITH <name> AS PROCEDURE ([ <arg_name> <arg_data_type> ]) [ , ... ] )
  RETURNS { <result_data_type> [ [ NOT ] NULL ] | TABLE ( [ <col_name> <col_data_type> [ , ... ] ] ) }
  LANGUAGE { SCALA | JAVA }
  RUNTIME_VERSION = '<scala_or_java_runtime_version>'
  PACKAGES = ( 'com.snowflake:snowpark:<version>' [, '<package_name_and_version>' ...] )
  [ IMPORTS = ( '<stage_path_and_file_name_to_read>' [, '<stage_path_and_file_name_to_read>' ...] ) ]
  HANDLER = '<fully_qualified_method_name>'
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ AS '<procedure_definition>' ]
  [ , <cte_nameN> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
CALL <name> ( [ [ <arg_name> => ] <arg> , ... ] )
  [ INTO :<snowflake_scripting_variable> ]
Copy

For Java and Scala procedures with staged handlers, use the following syntax:

WITH <name> AS PROCEDURE ([ <arg_name> <arg_data_type> ]) [ , ... ] )
  RETURNS { <result_data_type> [ [ NOT ] NULL ] | TABLE ( [ <col_name> <col_data_type> [ , ... ] ] ) }
  LANGUAGE { SCALA | JAVA }
  RUNTIME_VERSION = '<scala_or_java_runtime_version>'
  PACKAGES = ( 'com.snowflake:snowpark:<version>' [, '<package_name_and_version>' ...] )
  [ IMPORTS = ( '<stage_path_and_file_name_to_read>' [, '<stage_path_and_file_name_to_read>' ...] ) ]
  HANDLER = '<fully_qualified_method_name>'
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ , <cte_nameN> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
CALL <name> ( [ [ <arg_name> => ] <arg> , ... ] )
  [ INTO :<snowflake_scripting_variable> ]
Copy

JavaScript

WITH <name> AS PROCEDURE ([ <arg_name> <arg_data_type> ]) [ , ... ] )
  RETURNS <result_data_type> [ [ NOT ] NULL ]
  LANGUAGE JAVASCRIPT
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  AS '<procedure_definition>'
  [ , <cte_nameN> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
CALL <name> ( [ [ <arg_name> => ] <arg> , ... ] )
  [ INTO :<snowflake_scripting_variable> ]
Copy

Python

For in-line procedures, use the following syntax:

WITH <name> AS PROCEDURE ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> [ [ NOT ] NULL ] | TABLE ( [ <col_name> <col_data_type> [ , ... ] ] ) }
  LANGUAGE PYTHON
  RUNTIME_VERSION = '<python_version>'
  PACKAGES = ( 'snowflake-snowpark-python[==<version>]'[, '<package_name>[==<version>]' ... ])
  [ IMPORTS = ( '<stage_path_and_file_name_to_read>' [, '<stage_path_and_file_name_to_read>' ...] ) ]
  HANDLER = '<function_name>'
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ , <cte_nameN> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
  AS '<procedure_definition>'
CALL <name> ( [ [ <arg_name> => ] <arg> , ... ] )
  [ INTO :<snowflake_scripting_variable> ]
Copy

For a procedure in which the code is in a file on a stage, use the following syntax:

WITH <name> AS PROCEDURE ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> [ [ NOT ] NULL ] | TABLE ( [ <col_name> <col_data_type> [ , ... ] ] ) }
  LANGUAGE PYTHON
  RUNTIME_VERSION = '<python_version>'
  PACKAGES = ( 'snowflake-snowpark-python[==<version>]'[, '<package_name>[==<version>]' ... ])
  [ IMPORTS = ( '<stage_path_and_file_name_to_read>' [, '<stage_path_and_file_name_to_read>' ...] ) ]
  HANDLER = '<module_file_name>.<function_name>'
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ , <cte_nameN> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
CALL <name> ( [ [ <arg_name> => ] <arg> , ... ] )
  [ INTO :<snowflake_scripting_variable> ]
Copy

Snowflake Scripting

WITH <name> AS PROCEDURE ([ <arg_name> <arg_data_type> ]) [ , ... ] )
  RETURNS { <result_data_type> | TABLE ( [ <col_name> <col_data_type> [ , ... ] ] ) }
  LANGUAGE SQL
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  AS '<procedure_definition>'
  [ , <cte_nameN> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
CALL <name> ( [ [ <arg_name> => ] <arg> , ... ] )
  [ INTO :<snowflake_scripting_variable> ]
Copy

Required parameters

All languages

WITH name AS PROCEDURE ( [ arg_name arg_data_type ] [ , ... ] )

Specifies the identifier (name) and any input arguments for the procedure.

RETURNS result_data_type [ [ NOT ] NULL ]

Specifies the type of the result returned by the procedure.

Use NOT NULL to specify that the procedure must return only non-null values; the default is NULL, meaning that the procedure can return NULL.

  • For result_data_type, use the Snowflake data type that corresponds to the type of the language that you are using.

    Note

    Procedures you write in Java or Scala must have a return value. In Python, when a procedure returns no value, it is considered to be returning None.

    Note that regardless of handler language, the WITH clause for this command must include a RETURNS clause that defines a return type, even if the procedure does not explicitly return anything.

  • 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:

    WITH get_top_sales() AS PROCEDURE
  RETURNS TABLE (sales_date DATE, quantity NUMBER)
  ...
CALL get_top_sales();
    Copy

    Otherwise (e.g. if you are determining the column types during run time), you can omit the column names and types:

    WITH get_top_sales() AS PROCEDURE
  ...
  RETURNS TABLE ()
CALL get_top_sales();
    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

    RETURNS TABLE(...) is supported only in when the handler is written in the following languages:

As a practical matter, outside of a Snowflake Scripting block, the returned value cannot be used because the call cannot be part of an expression.

LANGUAGE language

Specifies the language of the procedure’s handler code.

Currently, the supported values for language include:

AS procedure_definition

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

Note the following:

  • For procedures for which the code is not in-line, omit the AS clause. This includes procedures whose handlers are on a stage.

    Instead, use the IMPORTS clause to specify the location of the file containing the code for the procedure. For details, see:

  • You must use string literal delimiters (' or $$) around procedure definition, even in Snowflake Scripting.

  • For procedures in JavaScript, if you are writing a string that contains newlines, you can use backquotes (also called “backticks”) around the string.

    The following example of a JavaScript procedure uses $$ and backquotes because the body of the procedure contains single quotes and double quotes:

    WITH proc3 AS PROCEDURE ()
  RETURNS VARCHAR
  LANGUAGE javascript
  AS
  $$
  var rs = snowflake.execute( { sqlText:
      `INSERT INTO table1 ("column 1")
          SELECT 'value 1' AS "column 1" ;`
      } );
  return 'Done.';
  $$
CALL proc3();
    Copy

  • Snowflake does not validate the handler code. However, invalid handler code will result in errors when you execute the command.

For more details about stored procedures, see Working with stored procedures.

CALL name ( [ [ arg_name => ] arg , ... ] )

Specifies the identifier (name) for the procedure to call and any input arguments.

You can either specify the input arguments by name (arg_name => arg) or by position (arg).

Note the following:

  • You must either specify all arguments by name or by position. You cannot specify some of the arguments by name and other arguments by position.

    When specifying an argument by name, you cannot use double quotes around the argument name.

  • If two functions or two procedures have the same name but different argument types, you can use the argument names to specify which function or procedure to execute, if the argument names are different. Refer to Overloading procedures and functions.

Java, Python, or Scala

RUNTIME_VERSION = 'language_runtime_version'

The language runtime version to use. Currently, the supported versions are:

  • Java: 11

  • Python:

    • 3.9

    • 3.10

    • 3.11

  • Scala: 2.12

PACKAGES = ( 'snowpark_package_name' [, 'package_name' ...] )

A comma-separated list of the names of packages deployed in Snowflake that should be included in the handler code’s execution environment. The Snowpark package is required for procedures, so it must always be referenced in the PACKAGES clause. For more information about Snowpark, see Snowpark API.

By default, the environment in which Snowflake runs procedures includes a selected set of packages for supported languages. When you reference these packages in the PACKAGES clause, it is not necessary to reference a file containing the package in the IMPORTS clause because the package is already available in Snowflake.

For the list of supported packages and versions for a given language, query the INFORMATION_SCHEMA.PACKAGES view, specifying the language. For example:

SELECT * FROM information_schema.packages WHERE language = '<language>';
Copy

where language is java, python, or scala.

The syntax for referring to a package in the PACKAGES clause varies by the package’s language, as described below.

  • Java

    Specify the package name and version number using the following form:

    domain:package_name:version
    Copy

    To specify the latest version, specify latest for version.

    For example, to include a package from the latest Snowpark library in Snowflake, use the following:

    PACKAGES = ('com.snowflake:snowpark:latest')
    Copy

    When specifying a package from the Snowpark library, you must specify version 1.3.0 or later.

  • Python

    Snowflake includes a large number of packages available through Anaconda; for more information, see Using third-party packages.

    Specify the package name and version number using the following form:

    package_name[==version]
    Copy

    To specify the latest version, omit the version number.

    For example, to include the spacy package version 2.3.5 (along with the latest version of the required Snowpark package), use the following:

    PACKAGES = ('snowflake-snowpark-python', 'spacy==2.3.5')
    Copy

    When specifying a package from the Snowpark library, you must specify version 0.4.0 or later. Omit the version number to use the latest version available in Snowflake.

  • Scala

    Specify the package name and version number using the following form:

    domain:package_name:version
    Copy

    To specify the latest version, specify latest for version.

    For example, to include a package from the latest Snowpark library in Snowflake, use the following:

    PACKAGES = ('com.snowflake:snowpark:latest')
    Copy

    Snowflake supports using Snowpark version 0.9.0 or later in a Scala procedure. Note, however, that these versions have limitations. For example, versions prior to 1.1.0 do not support the use of transactions in a procedure.

HANDLER = 'fully_qualified_method_name'

  • Python

    Use the name of the procedure’s function or method. This can differ depending on whether the code is in-line or referenced at a stage.

    • When the code is in-line, you can specify just the function name, as in the following example:

      WITH myproc AS PROCEDURE()
  ...
  HANDLER = 'run'
  AS
  $$
  def run(session):
    ...
  $$
CALL myproc();
      Copy

    • When the code is imported from a stage, specify the fully-qualified handler function name as <module_name>.<function_name>.

      WITH myproc AS PROCEDURE()
  ...
  IMPORTS = ('@mystage/my_py_file.py')
  HANDLER = 'my_py_file.run'
CALL myproc();
      Copy

  • Java and Scala

    Use the fully-qualified name of the method or function for the procedure. This is typically in the following form:

    com.my_company.my_package.MyClass.myMethod
    Copy

    where:

    com.my_company.my_package
    Copy

    corresponds to the package containing the object or class:

    package com.my_company.my_package;
    Copy

Optional parameters

All languages

CALLED ON NULL INPUT or . RETURNS NULL ON NULL INPUT | STRICT

Specifies the behavior of the procedure when called with null inputs. In contrast to system-defined functions, which always return null when any input is null, procedures can handle null inputs, returning non-null values even when an input is null:

  • CALLED ON NULL INPUT will always call the procedure with null inputs. It is up to the procedure to handle such values appropriately.

  • RETURNS NULL ON NULL INPUT (or its synonym STRICT) will not call the procedure if any input is null, so the statements inside the procedure will not be executed. Instead, a null value will always be returned. Note that the procedure might still return null for non-null inputs.

Default: CALLED ON NULL INPUT

INTO :snowflake_scripting_variable

Sets the specified Snowflake Scripting variable to the return value of the stored procedure.

Java, Python, or Scala

IMPORTS = ( 'stage_path_and_file_name_to_read' [, 'stage_path_and_file_name_to_read' ...] )

The location (stage), path, and name of the file(s) to import. You must set the IMPORTS clause to include any files that your procedure depends on:

  • If you are writing an in-line procedure, you can omit this clause, unless your code depends on classes defined outside the procedure or resource files.

  • Java or Scala: If you are writing a procedure whose handler will be compiled code, you must also include a path to the JAR file containing the procedure’s handler.

  • Python: If your procedure’s code will be on a stage, you must also include a path to the module file your code is in.

Each file in the IMPORTS clause must have a unique name, even if the files are in different subdirectories or different stages.

Usage notes

General usage

  • Procedures are not atomic; if one statement in a procedure fails, the other statements in the procedure are not necessarily rolled back. For information about procedures and transactions, see Transaction management.

  • A procedure can return only a single value, such as a string (for example, a success/failure indicator) or a number (for example, an error code). If you need to return more extensive information, you can return a VARCHAR that contains values separated by a delimiter (such as a comma), or a semi-structured data type, such as VARIANT.

  • Regarding metadata:

    Attention

    Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.

Syntax

  • Similar to when a WITH clause is used with a SELECT statement, a WITH clause used with CALL supports specifying multiple CTEs separated by commas, in addition to the procedure definition. However, it is not possible to pass tabular values produced by a WITH clause to the CALL clause.

    It is, however, possible to specify a simple variable whose value is assigned in the WITH clause.

  • The CALL clause must occur last in the syntax.

Privileges

  • Creating and calling a procedure with this command does not require a role with CREATE PROCEDURE schema privileges.

  • The procedure’s handler code will be able to perform only actions permitted for the role assigned to the person who ran this command.

Language-specific

Examples

The following example creates and calls a procedure, specifying the arguments by position:

WITH copy_to_table AS PROCEDURE (fromTable STRING, toTable STRING, count INT)
  RETURNS STRING
  LANGUAGE SCALA
  RUNTIME_VERSION = '2.12'
  PACKAGES = ('com.snowflake:snowpark:latest')
  HANDLER = 'DataCopy.copyBetweenTables'
  AS
  $$
    object DataCopy
    {
      def copyBetweenTables(session: com.snowflake.snowpark.Session, fromTable: String, toTable: String, count: Int): String =
      {
        session.table(fromTable).limit(count).write.saveAsTable(toTable)
        return "Success"
      }
    }
  $$
  CALL copy_to_table('table_a', 'table_b', 5);
Copy

The following example creates and calls a procedure, specifying the arguments by name:

WITH copy_to_table AS PROCEDURE (fromTable STRING, toTable STRING, count INT)
  RETURNS STRING
  LANGUAGE SCALA
  RUNTIME_VERSION = '2.12'
  PACKAGES = ('com.snowflake:snowpark:latest')
  HANDLER = 'DataCopy.copyBetweenTables'
  AS
  $$
    object DataCopy
    {
      def copyBetweenTables(session: com.snowflake.snowpark.Session, fromTable: String, toTable: String, count: Int): String =
      {
        session.table(fromTable).limit(count).write.saveAsTable(toTable)
        return "Success"
      }
      }
    }
  $$
  CALL copy_to_table(
    toTable => 'table_b',
    count => 5,
    fromTable => 'table_a');
Copy

For additional examples, refer to the following topics:

For procedure examples, see Working with stored procedures.

Language: English