A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://docs.snowflake.com/en/sql-reference/sql/call-with below:

Website Navigation


CALL (with anonymous procedure) | Snowflake Documentation

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.

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

Java, Python, or Scala
RUNTIME_VERSION = 'language_runtime_version'

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

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.

HANDLER = 'fully_qualified_method_name'
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:

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:

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 Syntax Privileges 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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4