Categories:

DDL for User-Defined Functions, External Functions, and Stored Procedures

CREATE PROCEDURE

Creates a new stored procedure.

A procedure can be written in one of the following languages:

See also:

ALTER PROCEDURE, DROP PROCEDURE , SHOW PROCEDURES , DESCRIBE PROCEDURE

CALL

Syntax

Snowpark (Java and Scala)

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

CREATE [ OR REPLACE ] PROCEDURE <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS <result_data_type> [ NOT NULL ]
  LANGUAGE { SCALA | JAVA }
  RUNTIME_VERSION = '<scala_or_java_runtime_version>'
  PACKAGES = ( '<fully_qualified_package_name_of_library_to_use>` )
  [ 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 { CALLER | OWNER } ]
  AS '<procedure_definition>'

For pre-compiled stored procedures in Scala and Java, use the following syntax:

CREATE [ OR REPLACE ] PROCEDURE <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS <result_data_type> [ NOT NULL ]
  LANGUAGE { SCALA | JAVA }
  RUNTIME_VERSION = '<scala_or_java_runtime_version>'
  PACKAGES = ( '<fully_qualified_package_name_of_library_to_use>` )
  [ 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 { CALLER | OWNER } ]

JavaScript

CREATE [ OR REPLACE ] PROCEDURE <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  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 { CALLER | OWNER } ]
  AS '<procedure_definition>'

Important

JavaScript is case-sensitive, whereas SQL is not. See Case-sensitivity in JavaScript Arguments for important information about using stored procedure argument names in the JavaScript code.

Snowpark (Python)

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

CREATE [ OR REPLACE ] PROCEDURE <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS <result_data_type> [ NOT NULL ]
  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 } } ]
  [ VOLATILE | IMMUTABLE ] -- Note: VOLATILE and IMMUTABLE are deprecated.
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { CALLER | OWNER } ]
  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> ] [ , ... ] )
  RETURNS <result_data_type> [ NOT NULL ]
  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 } } ]
  [ VOLATILE | IMMUTABLE ] -- Note: VOLATILE and IMMUTABLE are deprecated.
  [ COMMENT = '<string_literal>' ]
  [ EXECUTE AS { CALLER | OWNER } ]

Snowflake Scripting

CREATE [ OR REPLACE ] PROCEDURE <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  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 { CALLER | OWNER } ]
  AS <procedure_definition>

Note

If you are creating a Snowflake Scripting procedure in SnowSQL or the classic web interface, you must use string literal delimiters (' or $$) around procedure definition. See Using Snowflake Scripting in SnowSQL and the Classic Web Interface.

Required Parameters

All Languages

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

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

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.

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

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

    Note

    Currently, in the RETURNS TABLE(...) clause in CREATE PROCEDURE, you cannot specify GEOGRAPHY as a column type.

    CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
    RETURNS TABLE(g GEOGRAPHY)
    ...
    

    If you do so, calling the stored procedure results in the error:

    CALL test_return_geography_table_1();
    
    Stored procedure execution error: data type of returned table does not match expected returned table type
    

    To work around this, you can omit the column arguments and types in RETURNS TABLE().

    CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
    RETURNS TABLE()
    ...
    

    RETURNS TABLE(...) is supported only in Snowflake Scripting stored procedures that return a table. See RETURN.

As a practical matter, 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:

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 pre-compiled stored procedures in Snowpark Scala and Java, and Python stored procedures whose code is on a stage.

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

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

  • 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);
    
    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.';
      $$;
    
  • For languages other than Snowflake Scripting, 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.

Snowpark (Java, Python, or Scala)

RUNTIME_VERSION = 'language_runtime_version'

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

  • Java: 11

  • Python: 3.8

  • Scala: 2.12

PACKAGES = ( 'fully_qualified_package_name_of_library_to_use' )

The fully qualified package name of the Snowpark library. For Python, you can additiionally specify the names and versions of packages provided through Anaconda.

Specify the fully qualified package name for the Snowpark library in the following format:

  • For code in Java or Scala:

    com.snowflake:snowpark:<version>
    
  • For code in Python:

    snowflake-snowpark-python==<version>
    

where version is the version number. To specify the latest version:

  • For Java or Scala, specify latest.

  • For Python, omit the version number.

    Note

    Specify a version that is at least the minimum supported version or higher:

    • For Java, specify the version 1.3.0 or later.

    • For Python, specify the version 0.4.0 or later.

    • For Scala, specify the version 1.1.0 or later.

      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.

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

select * from information_schema.packages where language = '<language>';

where language is java, python, or scala.

HANDLER = 'fully_qualified_method_name'

  • For Python, 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):
        ...
      $$;
      
    • 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';
      
  • For Java and Scala, 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
    

    where:

    com.my_company.my_package
    

    corresponds to the package containing the object or class:

    package com.my_company.my_package;
    

Optional Parameters

All Languages

[ [ 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 CALLER or . EXECUTE AS OWNER

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 the statement CREATE PROCEDURE ... EXECUTE AS CALLER, then in the future the procedure will execute as a caller’s rights procedure.

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

By default (if neither OWNER nor CALLER is specified explicitly at the time the procedure is created), 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

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

  • Java or Scala: If you are writing a pre-compiled stored procedure, you must also include a path to the JAR file containing the definition of the stored procedure.

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

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 procedure code in Java or Scala, the TARGET_PATH clause 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.

Note

This clause is not needed for stored procedures whose code is in Python.

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.

Usage Notes

  • For all stored procedures:

    • Stored procedures support overloading. Two procedures can have the same name if they have a different number of parameters or different data types for their parameters.

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

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

    Tip

    If your organization uses a mix of caller’s rights and owner’s rights stored procedures, you might want to use a naming convention for your stored procedures to indicate whether an individual stored procedure is a caller’s rights stored procedure or an owner’s rights stored procedure.

  • For JavaScript stored procedures:

    • A JavaScript stored 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.

  • For Snowpark (Java) stored procedures, see the known limitations.

  • For Snowpark (Python) stored procedures, see the known limitations.

  • For Snowpark (Scala) stored procedures, see the known limitations.

Examples

This creates a trivial stored procedure that returns a hard-coded value. This is unrealistic, but shows the basic SQL syntax with minimal JavaScript code:

create or replace procedure sp_pi()
    returns float not null
    language javascript
    as
    $$
    return 3.1415926;
    $$
    ;

This shows a more realistic example that includes a call to the JavaScript API. A more extensive version of this procedure could allow a user to insert data into a table that the user didn’t have privileges to insert into directly. JavaScript statements could check the input parameters and execute the SQL INSERT only if certain requirements were met.

create or replace procedure stproc1(FLOAT_PARAM1 FLOAT)
    returns string
    language javascript
    strict
    execute as owner
    as
    $$
    var sql_command = 
     "INSERT INTO stproc_test_table1 (num_col1) VALUES (" + FLOAT_PARAM1 + ")";
    try {
        snowflake.execute (
            {sqlText: sql_command}
            );
        return "Succeeded.";   // Return a success/error indicator.
        }
    catch (err)  {
        return "Failed: " + err;   // Return a success/error indicator.
        }
    $$
    ;

For more examples, see Working with Stored Procedures.

Back to top