Creating a stored procedure¶

Create a procedure¶

1. Write procedure logic as a handler using one of several supported languages, including Python, Java, and Scala.

2. Choose a tool or API to create the procedure with the handler you wrote.

   For more information about each of these, see Tools for creating procedures.

   SQL	Use SQL and write logic in one of several languages.
   Snowpark	Use the Snowpark API for Java, Python, or Scala.
   Command line	Execute Snowflake CLI commands to create the procedure.
   Python API	Execute client-side Python commands to create the procedure.
   REST	Make requests to a RESTful API to create the procedure.

3. Execute the procedure using one of several tools, depending on your needs.

Tools for creating procedures¶

You can create a procedure using any of several methods available with Snowflake, depending on the language and skill set you have available. Choose the tool that’s right for your needs from the following table.

Key properties¶

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

Procedure name:

The procedure name does not need to match the name of the handler. For more about name constraints and conventions, see Naming and overloading procedures and UDFs.

Arguments:

For more information on requirements, see Defining arguments for UDFs and stored procedures.

Return type:

For information about how Snowflake maps SQL data types to handler data types, see Data Type Mappings Between SQL and Handler Languages.

Handler name:

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.

Dependencies:

For a handler written in Java, Python, or Scala, you might also need to specify the Snowpark library, such as when creating the procedure.

For more about making dependencies available to your handler, see Making dependencies available to your code.

Handler language runtime:

When the handler language is Java, Python, or Scala, specify the runtime version to indicate which supported runtime version to use. Keep in mind that if you use the default version, that default will change over time.

Access control requirements¶

A role used to execute this operation must have the following privileges at a minimum:

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage notes¶

Create a stored procedure with SQL¶

You can create a stored procedure with SQL using the following steps.

1. 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 and tools.

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

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