Creating a stored procedure¶

You create a stored procedure with a CREATE PROCEDURE command that specifies the procedure’s properties, including the handler to use for its logic.

This topic lists the steps to create a procedure. For examples, see the CREATE PROCEDURE reference.

Note

You can also create and call a procedure that isn’t stored for later use. Many of the properties for that kind of procedure are the same as for a stored procedure. For more information, see CALL (with anonymous procedure).

Creating a stored procedure¶

You create a stored procedure with the following steps:

Write handler code that executes when the procedure is called.

You can use one of the supported handler languages. For more information, see Supported languages.
Choose whether you’ll keep the handler code in-line with the CREATE PROCEDURE SQL statement or refer to it on a stage.

Each has its advantages. For more information, see Keeping handler code in-line or on a stage.

Execute a CREATE PROCEDURE statement in SQL, specifying properties of the procedure.

Code in the following example creates a procedure called myProc with a in-line handler MyClass.myMethod. The handler language is Java, which (like procedure handlers written in Scala and Python) requires a Session object from the Snowpark library. Here, the PACKAGES clause refers to the Snowpark library included with Snowflake.

CREATE OR REPLACE PROCEDURE myProc(fromTable STRING, toTable STRING, count INT)
  RETURNS STRING
  LANGUAGE JAVA
  RUNTIME_VERSION = '11'
  PACKAGES = ('com.snowflake:snowpark:latest')
  HANDLER = 'MyClass.myMethod'
  AS
  $$
    import com.snowflake.snowpark_java.*;

    public class MyClass
    {
      public String myMethod(Session session, String fromTable, String toTable, int count)
      {
        session.table(fromTable).limit(count).write().saveAsTable(toTable);
        return "Success";
      }
    }
  $$;

Copy

The following describes some of the properties required or typically used when creating a procedure.

Procedure name.

The name does not need to match the name of the handler. The CREATE PROCEDURE statement associates the procedure name with the handler.

For more about name constraints and conventions, see Naming and overloading procedures and UDFs.
Procedure arguments, if any.

See Defining arguments for UDFs and stored procedures.
Return type with the RETURNS clause.

For information about how Snowflake maps SQL data types to handler data types, see SQL-Java Data Type Mappings.

Handler name with the HANDLER clause.

When required, this is the name of the class or method containing code that executes when the procedure is called. You need specify a handler name only for handlers written in Java, Python, and Scala. For JavaScript and Snowflake Scripting handlers, all code specified in-line will be executed as the handler.

The following table describes the form of the HANDLER clause’s value based on the handler language.

Handler Language	Value Form
Java	Class and method name. For example: `MyClass.myMethod`
JavaScript	None.
Python	Class and method name if a class is used; otherwise, function name. For example: `module.my_function` or `my_function`
Scala	Object and method name. For example: `MyObject.myMethod`
Snowflake Scripting	None.

Dependencies required by the handler, if any, using the IMPORTS or PACKAGES clauses.

For a handler written in Java, Python, or Scala, be sure to specify the Snowpark library, as described in the CREATE PROCEDURE reference.

For more about making dependencies available to your handler, see Making dependencies available to your code.
Handler language runtime with RUNTIME_VERSION clause.

When the handler language is Java, Python, or Scala, use the RUNTIME_VERSION clause to specify which supported runtime version to use. Omitting the clause will prompt Snowflake to use the default, which may change in the future.

Examples¶

The following sections contain examples that create stored procedures in different languages:

For examples of Java stored procedures, see Writing stored procedures in Java.
For examples of JavaScript stored procedures, see Writing stored procedures in JavaScript.
For examples of Python stored procedures, see Writing stored procedures in Python.
For examples of Scala stored procedures, see Writing stored procedures in Scala.
For examples of Snowflake Scripting stored procedures, see Writing stored procedures in Snowflake Scripting.

The following sections provide an example of an in-line handler and a staged handler.

In-line handler¶

Code in the following example creates a procedure called my_proc with an in-line Python handler function run. Through the PACKAGES clause, the code references the included Snowpark library for Python, whose Session is required when Python is the procedure handler language.

CREATE OR REPLACE PROCEDURE my_proc(from_table STRING, to_table STRING, count INT)
  RETURNS STRING
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.9'
  PACKAGES = ('snowflake-snowpark-python')
  HANDLER = 'run'
AS
$$
def run(session, from_table, to_table, count):
  session.table(from_table).limit(count).write.save_as_table(to_table)
  return "SUCCESS"
$$;

Copy

Staged handler¶

Code in the following example creates a procedure called my_proc with an staged Java handler method MyClass.myMethod. Through the PACKAGES clause, the code references the included Snowpark library for Java, whose Session is required when Java is the procedure handler language. With the IMPORTS clause, the code references the staged JAR file containing the handler code.

CREATE OR REPLACE PROCEDURE my_proc(fromTable STRING, toTable STRING, count INT)
  RETURNS STRING
  LANGUAGE JAVA
  RUNTIME_VERSION = '11'
  PACKAGES = ('com.snowflake:snowpark:latest')
  IMPORTS = ('@mystage/myjar.jar')
  HANDLER = 'MyClass.myMethod';

Copy