CREATE PROCEDURE¶

Java handler¶

You can create a stored procedure that either includes its handler code in-line, or refers to its handler code in a JAR file. For more information, see Keeping handler code in-line or on a stage.

For in-line stored procedures, use the following syntax:

CREATE [ OR REPLACE ] [ SECURE ] PROCEDURE <name> (
    [ <arg_name> <arg_data_type> [ DEFAULT <default_value> ] ] [ , ... ] )
  [ COPY GRANTS ]
  RETURNS { <result_data_type> [ [ NOT ] NULL ] | TABLE ( [ <col_name> <col_data_type> [ , ... ] ] ) }
  LANGUAGE JAVA
  RUNTIME_VERSION = '<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>'
  [ EXTERNAL_ACCESS_INTEGRATIONS = ( <name_of_integration> [ , ... ] ) ]
  [ SECRETS = ('<secret_variable_name>' = <secret_name> [ , ... ] ) ]
  [ TARGET_PATH = '<stage_path_and_file_name_to_write>' ]
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ { VOLATILE | IMMUTABLE } ] -- Note: VOLATILE and IMMUTABLE are deprecated.
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { OWNER | CALLER | RESTRICTED CALLER } ]
  AS '<procedure_definition>'

For a stored procedure that uses a precompiled handler, use the following syntax.

CREATE [ OR REPLACE ] [ SECURE ] PROCEDURE <name> (
    [ <arg_name> <arg_data_type> [ DEFAULT <default_value> ] ] [ , ... ] )
  [ COPY GRANTS ]
  RETURNS { <result_data_type> [ [ NOT ] NULL ] | TABLE ( [ <col_name> <col_data_type> [ , ... ] ] ) }
  LANGUAGE JAVA
  RUNTIME_VERSION = '<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>'
  [ EXTERNAL_ACCESS_INTEGRATIONS = ( <name_of_integration> [ , ... ] ) ]
  [ SECRETS = ('<secret_variable_name>' = <secret_name> [ , ... ] ) ]
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ] -- Note: VOLATILE and IMMUTABLE are deprecated.
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { OWNER | CALLER | RESTRICTED CALLER } ]

JavaScript handler¶

CREATE [ OR REPLACE ] [ SECURE ] PROCEDURE <name> (
    [ <arg_name> <arg_data_type> [ DEFAULT <default_value> ] ] [ , ... ] )
  [ COPY GRANTS ]
  RETURNS <result_data_type> [ NOT NULL ]
  LANGUAGE JAVASCRIPT
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ] -- Note: VOLATILE and IMMUTABLE are deprecated.
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { OWNER | CALLER | RESTRICTED CALLER } ]
  AS '<procedure_definition>'

Python handler¶

For in-line stored procedures, use the following syntax:

CREATE [ OR REPLACE ] [ SECURE ] PROCEDURE <name> (
    [ <arg_name> <arg_data_type> [ DEFAULT <default_value> ] ] [ , ... ] )
  [ COPY GRANTS ]
  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>'
  [ EXTERNAL_ACCESS_INTEGRATIONS = ( <name_of_integration> [ , ... ] ) ]
  [ SECRETS = ('<secret_variable_name>' = <secret_name> [ , ... ] ) ]
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ { VOLATILE | IMMUTABLE } ] -- Note: VOLATILE and IMMUTABLE are deprecated.
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { OWNER | CALLER | RESTRICTED CALLER }]
  AS '<procedure_definition>'

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

CREATE [ OR REPLACE ] PROCEDURE <name> (
    [ <arg_name> <arg_data_type> [ DEFAULT <default_value> ] ] [ , ... ] )
  [ COPY GRANTS ]
  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>'
  [ EXTERNAL_ACCESS_INTEGRATIONS = ( <name_of_integration> [ , ... ] ) ]
  [ SECRETS = ('<secret_variable_name>' = <secret_name> [ , ... ] ) ]
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ { VOLATILE | IMMUTABLE } ] -- Note: VOLATILE and IMMUTABLE are deprecated.
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { OWNER | CALLER | RESTRICTED CALLER } ]

Scala handler¶

You can create a stored procedure that either includes its handler code in-line, or refers to its handler code in a JAR file. For more information, see Keeping handler code in-line or on a stage.

For in-line stored procedures, use the following syntax:

CREATE [ OR REPLACE ] [ SECURE ] PROCEDURE <name> (
    [ <arg_name> <arg_data_type> [ DEFAULT <default_value> ] ] [ , ... ] )
  [ COPY GRANTS ]
  RETURNS { <result_data_type> [ [ NOT ] NULL ] | TABLE ( [ <col_name> <col_data_type> [ , ... ] ] ) }
  LANGUAGE SCALA
  RUNTIME_VERSION = '<scala_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>'
  [ TARGET_PATH = '<stage_path_and_file_name_to_write>' ]
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ { VOLATILE | IMMUTABLE } ] -- Note: VOLATILE and IMMUTABLE are deprecated.
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { OWNER | CALLER | RESTRICTED CALLER } ]
  AS '<procedure_definition>'

For a stored procedure that uses a precompiled handler, use the following syntax.

CREATE [ OR REPLACE ] [ SECURE ] PROCEDURE <name> (
    [ <arg_name> <arg_data_type> [ DEFAULT <default_value> ] ] [ , ... ] )
  [ COPY GRANTS ]
  RETURNS { <result_data_type> [ [ NOT ] NULL ] | TABLE ( [ <col_name> <col_data_type> [ , ... ] ] ) }
  LANGUAGE SCALA
  RUNTIME_VERSION = '<scala_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 } } ]
  [ VOLATILE | IMMUTABLE ] -- Note: VOLATILE and IMMUTABLE are deprecated.
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { OWNER | CALLER | RESTRICTED CALLER } ]

Snowflake Scripting handler¶

CREATE [ OR REPLACE ] PROCEDURE <name> (
    [ <arg_name> <arg_data_type> [ DEFAULT <default_value> ] ] [ , ... ] )
  [ COPY GRANTS ]
  RETURNS { <result_data_type> | TABLE ( [ <col_name> <col_data_type> [ , ... ] ] ) }
  [ NOT NULL ]
  LANGUAGE SQL
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ { VOLATILE | IMMUTABLE } ] -- Note: VOLATILE and IMMUTABLE are deprecated.
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { OWNER | CALLER | RESTRICTED CALLER } ]
  AS <procedure_definition>

All languages¶

name ( [ arg_name arg_data_type [ DEFAULT default_value ] ] [ , ... ] )

Specifies the identifier (name), any input arguments, and the default values for any optional arguments for the stored procedure.

• For the identifier:

  • The identifier does not need to be unique for the schema in which the procedure is created because stored procedures are identified and resolved by the combination of the name and argument types.

  • The identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. “My object”). Identifiers enclosed in double quotes are also case-sensitive. See Identifier requirements.

• For the input arguments:

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

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

    • For Java stored procedures, see SQL-Java Data Type Mappings.

    • For JavaScript stored procedures, see SQL and JavaScript data type mapping.

    • For Python stored procedures, see SQL-Python Data Type Mappings.

    • For Scala stored procedures, see SQL-Scala Data Type Mappings.

    • For Snowflake Scripting, a SQL data type.

    Note

    For stored procedures you write in Java, Python, or Scala (which use Snowpark APIs), omit the argument for the Snowpark Session object.

    The Session argument is not a formal parameter that you specify in CREATE PROCEDURE or CALL. When you call your stored procedure, Snowflake automatically creates a Session object and passes it to the handler function for your stored procedure.

  • To indicate that an argument is optional, use DEFAULT default_value to specify the default value of the argument. For the default value, you can use a literal or an expression.

    If you specify any optional arguments, you must place these after the required arguments.

    If a procedure has optional arguments, you cannot define additional procedures with the same name and different signatures.

    For details, see Specify optional arguments.

RETURNS result_data_type [ NOT NULL ]

Specifies the type of the result returned by the stored procedure.

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

  • For Java stored procedures, see SQL-Java Data Type Mappings.

  • For JavaScript stored procedures, see SQL and JavaScript data type mapping.

  • For Python stored procedures, see SQL-Python Data Type Mappings.

  • For Scala stored procedures, see SQL-Scala Data Type Mappings.

  • For Snowflake Scripting, a SQL data type.

  Note

  Stored procedures you write in Snowpark (Java or Scala) must have a return value. In Snowpark (Python), when a stored procedure returns no value, it is considered to be returning None. Note that every CREATE PROCEDURE statement 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:

  CREATE OR REPLACE PROCEDURE get_top_sales()
    RETURNS TABLE (sales_date DATE, quantity NUMBER)
  ...
  

  Copy

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

  CREATE OR REPLACE PROCEDURE get_top_sales()
    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

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

  • Java

  • Python

  • Scala

  • Snowflake Scripting

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 stored procedure code. Note that this is optional for stored procedures written with Snowflake Scripting.

Currently, the supported values for language include:

• JAVA (for Java)

• JAVASCRIPT (for JavaScript)

• PYTHON (for Python)

• SCALA (for Scala)

• SQL (for Snowflake Scripting)

Default: SQL.

AS procedure_definition

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

Note the following:

• For stored procedures for which the code is not in-line, omit the AS clause. This includes stored procedures with staged handlers.

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

  • Writing stored procedures in Python

  • Writing stored procedures in Java

  • Writing stored procedures in Scala

  For more information on in-line and staged handlers, see Keeping handler code in-line or on a stage.

• You must use string literal delimiters (' or $$) around procedure definition if:

  • You are using a language other than Snowflake Scripting.

  • You are creating a Snowflake Scripting procedure in SnowSQL or the Classic Console. See Using Snowflake Scripting in SnowSQL, the Classic Console, and Python Connector.

• For stored 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 stored procedure uses $$ and backquotes because the body of the stored procedure contains single quotes and double quotes:

  CREATE OR REPLACE TABLE table1 ("column 1" VARCHAR);
  

  Copy

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

  Copy

• Snowflake does not completely validate the code when you execute the CREATE PROCEDURE command.

  For example, for Snowpark (Scala) stored procedures, the number and types of input arguments are validated, but the body of the function is not validated. If the number or types do not match (e.g. if the Snowflake data type NUMBER is used when the argument is a non-numeric type), executing the CREATE PROCEDURE command causes an error.

  If the code is not valid, the CREATE PROCEDURE command will succeed, and errors will be returned when the stored procedure is called.

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

Java¶

RUNTIME_VERSION = 'language_runtime_version'

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

• 11

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 stored 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 stored 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. You can also specify the package version.

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

SELECT * FROM INFORMATION_SCHEMA.PACKAGES WHERE LANGUAGE = 'java';

Copy

To specify the package name and version number use 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.

HANDLER = 'fully_qualified_method_name'

Use the fully qualified name of the method or function for the stored 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

Python¶

RUNTIME_VERSION = 'language_runtime_version'

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

• 3.9

• 3.10

• 3.11

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 stored 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 stored 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. You can also specify the package version.

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

SELECT * FROM INFORMATION_SCHEMA.PACKAGES WHERE LANGUAGE = 'python';

Copy

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

To specify the package name and version number use 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.

Preview Feature — Open

Specifying a range of Python package versions is available as a preview feature to all accounts.

You can specify package versions by using these version specifiers: ==, <=, >=, <,or >.

For example:

-- Use version 1.2.3 or higher of the NumPy package.
PACKAGES=('numpy>=1.2.3')

Copy

This feature is not available in the People’s Republic of China.

HANDLER = 'fully_qualified_method_name'

Use the name of the stored 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:

  CREATE OR REPLACE PROCEDURE MYPROC(from_table STRING, to_table STRING, count INT)
    ...
    HANDLER = 'run'
  AS
  $$
  def run(session, from_table, to_table, count):
    ...
  $$;
  

  Copy

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

  CREATE OR REPLACE PROCEDURE MYPROC(from_table STRING, to_table STRING, count INT)
    ...
    IMPORTS = ('@mystage/my_py_file.py')
    HANDLER = 'my_py_file.run';
  

  Copy

Scala¶

RUNTIME_VERSION = 'language_runtime_version'

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

• 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 stored 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 stored 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. You can also specify the package version.

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

SELECT * FROM INFORMATION_SCHEMA.PACKAGES WHERE LANGUAGE = 'scala';

Copy

To specify the package name and version number use 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 stored 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 stored procedure.

HANDLER = 'fully_qualified_method_name'

Use the fully qualified name of the method or function for the stored 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

All languages¶

SECURE

Specifies that the procedure is secure. For more information about secure procedures, see Protecting Sensitive Information with Secure UDFs and Stored Procedures.

[ [ NOT ] NULL ]

Specifies whether the stored procedure can return NULL values or must return only NON-NULL values.

The default is NULL (i.e. the stored procedure can return NULL).

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

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

• CALLED ON NULL INPUT will always call the stored 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 stored procedure if any input is null, so the statements inside the stored 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

VOLATILE | IMMUTABLE

Deprecated

Attention

These keywords are deprecated for stored procedures. These keywords are not intended to apply to stored procedures. In a future release, these keywords will be removed from the documentation.

COMMENT = 'string_literal'

Specifies a comment for the stored procedure, which is displayed in the DESCRIPTION column in the SHOW PROCEDURES output.

Default: stored procedure

EXECUTE AS OWNER or . EXECUTE AS CALLER or . EXECUTE AS RESTRICTED CALLER

Preview Feature — Open

Restricted caller’s rights (EXECUTE AS RESTRICTED CALLER) is a preview feature available to all accounts.

Specifies whether the stored procedure executes with the privileges of the owner (an “owner’s rights” stored procedure) or with the privileges of the caller (a “caller’s rights” stored procedure):

• If you execute CREATE PROCEDURE … EXECUTE AS OWNER, then the procedure will execute as an owner’s rights procedure.

• If you execute the statement CREATE PROCEDURE … EXECUTE AS CALLER, then in the future the procedure will execute as a caller’s rights procedure.

• If you execute the statement CREATE PROCEDURE … EXECUTE AS RESTRICTED CALLER, then in the future the procedure will execute as a caller’s rights procedure, but might not be able to run with all of the caller’s privileges. For more information, see Restricted caller’s rights.

If EXECUTE AS ... isn’t specified, the procedure runs as an owner’s rights stored procedure. Owner’s rights stored procedures have less access to the caller’s environment (for example, the caller’s session variables), and Snowflake defaults to this higher level of privacy and security.

For more information, see Understanding caller’s rights and owner’s rights stored procedures.

Default: OWNER

COPY GRANTS

Specifies to retain the access privileges from the original procedure when a new procedure is created using CREATE OR REPLACE PROCEDURE.

The parameter copies all privileges, except OWNERSHIP, from the existing procedure to the new procedure. The new procedure will inherit any future grants defined for the object type in the schema. By default, the role that executes the CREATE PROCEDURE statement owns the new procedure.

Note:

• The SHOW GRANTS output for the replacement procedure lists the grantee for the copied privileges as the role that executed the CREATE PROCEDURE statement, with the current timestamp when the statement was executed.

• The operation to copy grants occurs atomically in the CREATE PROCEDURE command (i.e. within the same transaction).

Java¶

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 stored procedure depends on:

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

• If you are writing a stored procedure with a staged handler, you must also include a path to the JAR file containing the stored procedure’s handler code.

• The IMPORTS definition cannot reference variables from arguments that are passed into the stored procedure.

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

TARGET_PATH = 'stage_path_and_file_name_to_write'

For stored procedures with inline handler code, specifies the location to which Snowflake should write the compiled code (JAR file) after compiling the source code specified in the procedure_definition. If this clause is omitted, Snowflake re-compiles the source code each time the code is needed.

If you specify this clause:

• You cannot set this to an existing file. Snowflake returns an error if the TARGET_PATH points to an existing file.

• If you specify both the IMPORTS and TARGET_PATH clauses, the file name in the TARGET_PATH clause must be different from each file name in the IMPORTS clause, even if the files are in different subdirectories or different stages.

• If you no longer need to use the stored procedure (e.g. if you drop the stored procedure), you must manually remove this JAR file.

EXTERNAL_ACCESS_INTEGRATIONS = ( integration_name [ , ... ] )

The names of external access integrations needed in order for this procedure’s handler code to access external networks.

An external access integration specifies network rules and secrets that specify external locations and credentials (if any) allowed for use by handler code when making requests of an external network, such as an external REST API.

SECRETS = ( 'secret_variable_name' = secret_name [ , ...  ] )

Assigns the names of secrets to variables so that you can use the variables to reference the secrets when retrieving information from secrets in handler code.

Secrets you specify here must be allowed by the external access integration specified as a value of this CREATE PROCEDURE command’s EXTERNAL_ACCESS_INTEGRATIONS parameter

This parameter’s value is a comma-separated list of assignment expressions with the following parts:

• secret_name as the name of the allowed secret.

  You will receive an error if you specify a SECRETS value whose secret isn’t also included in an integration specified by the EXTERNAL_ACCESS_INTEGRATIONS parameter.

• 'secret_variable_name' as the variable that will be used in handler code when retrieving information from the secret.

For more information, including an example, refer to Using the external access integration in a function or procedure.

Python¶

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 stored procedure depends on:

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

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

• The IMPORTS definition cannot reference variables from arguments that are passed into the stored procedure.

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

EXTERNAL_ACCESS_INTEGRATIONS = ( integration_name [ , ... ] )

The names of external access integrations needed in order for this procedure’s handler code to access external networks.

An external access integration specifies network rules and secrets that specify external locations and credentials (if any) allowed for use by handler code when making requests of an external network, such as an external REST API.

SECRETS = ( 'secret_variable_name' = secret_name [ , ...  ] )

Assigns the names of secrets to variables so that you can use the variables to reference the secrets when retrieving information from secrets in handler code.

Secrets you specify here must be allowed by the external access integration specified as a value of this CREATE PROCEDURE command’s EXTERNAL_ACCESS_INTEGRATIONS parameter

This parameter’s value is a comma-separated list of assignment expressions with the following parts:

• secret_name as the name of the allowed secret.

  You will receive an error if you specify a SECRETS value whose secret isn’t also included in an integration specified by the EXTERNAL_ACCESS_INTEGRATIONS parameter.

• 'secret_variable_name' as the variable that will be used in handler code when retrieving information from the secret.

For more information, including an example, refer to Using the external access integration in a function or procedure.

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 stored procedure depends on:

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

• If you are writing a stored procedure with a staged handler, you must also include a path to the JAR file containing the stored procedure’s handler code.

• The IMPORTS definition cannot reference variables from arguments that are passed into the stored procedure.

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

TARGET_PATH = 'stage_path_and_file_name_to_write'

For stored procedures with inline handler code, specifies the location to which Snowflake should write the compiled code (JAR file) after compiling the source code specified in the procedure_definition. If this clause is omitted, Snowflake re-compiles the source code each time the code is needed.

If you specify this clause:

• You cannot set this to an existing file. Snowflake returns an error if the TARGET_PATH points to an existing file.

• If you specify both the IMPORTS and TARGET_PATH clauses, the file name in the TARGET_PATH clause must be different from each file name in the IMPORTS clause, even if the files are in different subdirectories or different stages.

• If you no longer need to use the stored procedure (e.g. if you drop the stored procedure), you must manually remove this JAR file.