What is Stored Procedure

A stored procedure encapsulates logic written by a developer, allowing it to be called repeatedly within SQL. The logic contained within a stored procedure may perform a wide array of database operations, ranging from executing SQL statements to dynamically creating and executing SQL statements.

Stored Procedure Example

Let's look at a simple stored procedure named myproc with a Python handler named run. This procedure has the primary function of moving data from one table to another with a set limit on the number of rows transferred.

The SQL code for creating this stored procedure is as follows:

create or replace procedure myproc(from_table string, to_table string, count int)
  returns string
  language python
  runtime_version = '3.8'
  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"
$$;

Once the stored procedure is created, we can call the procedure using the CALL command. For instance:

CALL myproc('table_a', 'table_b', 5);

This would transfer five rows of data from table_a to table_b.

Supported Languages

Stored procedures can be written in several programming languages. The choice of language depends on the specific needs and requirements of the task, the developer's proficiency, and the available libraries or features within the language.

In Snowflake, you can write a stored procedure's handler – its logic – in any of the following languages:

• Java (using the Snowpark API)
• JavaScript
• Python (using the Snowpark API)
• Scala (using the Snowpark API)
• Snowflake Scripting (SQL)

Each language allows data manipulation within the constraints of the language and its runtime environment. No matter the handler language chosen, the procedure itself is created in the same way using SQL, specifying your handler and handler language.

Creating a Stored Procedure

You create a stored procedure with the following steps:

1. Write handler code
   This is the code that executes when the procedure is called. It should be designed to handle the specific database operations that the stored procedure is created for.

2. Decide on the handler code location
   The handler code can be kept in-line within the CREATE PROCEDURE SQL statement, or it can be referred to on a stage. A stage is a named file space and can be a table stage, a user stage, or a Snowflake stage.

3. Execute a CREATE PROCEDURE statement
   The statement is executed in SQL, and it specifies properties of the procedure.

Here is an example.

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";
    }
  }
$$;

In this example, a stored procedure myProc is created with an in-line handler MyClass.myMethod. The handler language is Java, and the handler requires a Session object from the Snowpark library, which is included in the PACKAGES clause.

When creating a procedure, the following properties are typically used:

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

• Procedure arguments
  At runtime, procedure argument values are bound to the procedure’s handler’s arguments. They may be bound based on matching names or by argument position, depending on the language you’re using for the handler.

• Return type
  Specified with the RETURNS clause.

• Handler name
  Specified with the HANDLER clause. When required, this is the name of the class or method containing code that executes when the procedure is called.

• Dependencies required by the handler
  These are specified using the IMPORTS or PACKAGES clauses.

• Handler language runtime
  Specified with the RUNTIME_VERSION clause.

In-line Handler

Code in the following example creates a procedure called my_proc with an in-line Python handler function run.

create or replace procedure my_proc(from_table string, to_table string, count int)
  returns string
  language python
  runtime_version = '3.8'
  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"
$$;

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';

Calling Stored Procedure

Once you've created a stored procedure, the next step is to call it and put it to use.

To call a stored procedure, you use the CALL command. The basic syntax is as follows:

CALL procedure_name(argument1, argument2, ...);

Privileges for Calling a Stored Procedure

In order to call a stored procedure, the user’s role must have the USAGE privilege for the stored procedure. The USAGE privilege allows a role to execute a stored procedure but not to manage it.

Specifying Arguments

If the stored procedure has arguments, you can specify those arguments by name or by position.

For example, consider the following stored procedure that accepts three arguments:

CREATE OR REPLACE PROCEDURE sp_concatenate_strings(
    first VARCHAR,
    second VARCHAR,
    third VARCHAR)
  RETURNS VARCHAR
  LANGUAGE SQL
  AS
  $$
  BEGIN
    RETURN first || second || third;
  END;
  $$;

When calling the procedure, you can specify the arguments by name:

CALL sp_concatenate_strings(
  first => 'one',
  second => 'two',
  third => 'three');

If you specify the arguments by name, you do not need to specify the arguments in any particular order:

CALL sp_concatenate_strings(
  third => 'three',
  first => 'one',
  second => 'two');

You can also specify the arguments by position:

CALL sp_concatenate_strings(
  'one',
  'two',
  'three');

References

https://docs.snowflake.com/en/sql-reference/stored-procedures-overview
https://docs.snowflake.com/en/sql-reference/stored-procedures-creating-sql
https://docs.snowflake.com/en/sql-reference/stored-procedures-calling