Stored procedures overview¶

You can write stored procedures to extend the system with procedural code. With a procedure, you can use branching, looping, and other programmatic constructs. You can reuse a procedure multiple times by calling it from other code.

With a stored procedure, you can:

Automate tasks that require multiple database operations performed frequently.
Dynamically create and execute database operations.
Execute code with the privileges of the role that owns the procedure, rather than with the privileges of the role that runs the procedure.

This allows the stored procedure owner to delegate the power to perform specified operations to users who otherwise could not do so. However, there are limitations on these owner’s rights stored procedures.

For example, imagine that you want to clean up a database by deleting data older than a specified date. You can execute the delete operation multiple times in your code, each time deleting data from a specific table. You can put all of those statements in a single stored procedure, then pass a parameter that specifies the cut-off date.

With the procedure deployed, you can call it to clean up the database. As your database changes, you can update the procedure to clean up additional tables; if there are multiple users who use the new cleanup command, they can call one procedure, rather than remember every table name and clean up each table individually.

A stored procedure is like a UDF, but the two differ in important ways. For more information, see Choosing whether to write a stored procedure or a user-defined function.

A procedure is just one way to extend Snowflake. For others, see the following:

Supported languages and tools¶

You can create and manage stored procedures (and other Snowflake entities) by using any of multiple tools, depending on how you prefer to work.

Language	Approach	Support
SQL With handler in Java, JavaScript, Python, Scala, or SQL Scripting	Write SQL code in Snowflake to create and manage Snowflake entities. Write the procedure’s logic in one of the supported handler languages.	Java JavaScript Python Scala SQL Scripting
Java, Python, or Scala Snowpark API	On the client, write code for operations that are pushed to Snowflake for processing.	Java Python Scala
Command-line interface Snowflake CLI	Use the command line to create and manage Snowflake entities, specifying properties as properties of JSON objects.	Managing Snowflake objects
Python Snowflake Python API	On the client, write code that executes management operations on Snowflake.	Managing stored procedures
REST Snowflake REST API	Make requests of RESTful endpoints to create and manage Snowflake entities.	Manage procedures

You write a procedure’s logic — its handler — in one of the supported languages. Once you have a handler, you can create a procedure with a CREATE PROCEDURE command, then call the procedure with a CALL statement.

From a stored procedure, you can return a single value or (where supported with the handler language) tabular data. For more information about supported return types, see CREATE PROCEDURE.

When choosing a language, consider also the handler locations supported. Not all languages support referring to the handler on a stage (the handler code must instead be in-line). For more information, see Keeping handler code in-line or on a stage.

Language	Handler Location
Java	In-line or staged
JavaScript	In-line
Python	In-line or staged
Scala	In-line or staged
Snowflake Scripting	In-line

Note

To both create and call an anonymous procedure, use CALL (with anonymous procedure). Creating and calling an anonymous procedure does not require a role with CREATE PROCEDURE schema privileges.

Stored procedure example¶

Code in the following example creates a stored procedure called myproc with a Python handler called run.

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

Code in the following example calls the stored procedure myproc.

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

Copy

Guidelines and constraints¶

Tips:: For tips on writing stored procedures, see Working with stored procedures.
Snowflake constraints:: You can ensure stability within the Snowflake environment by developing within Snowflake constraints. For more information, see Designing Handlers that Stay Within Snowflake-Imposed Constraints.
Naming:: Be sure to name procedures in a way that avoids collisions with other procedures. For more information, see Naming and overloading procedures and UDFs.
Arguments:: Specify the arguments for your stored procedure and indicate which arguments are optional. For more information, see Defining arguments for UDFs and stored procedures.
Data type mappings:: For each handler language, there’s a separate set of mappings between the language’s data types and the SQL types used for arguments and return values. For more about the mappings for each language, see Data Type Mappings Between SQL and Handler Languages.

Handler writing¶

Handler languages:: For language-specific content on writing a handler, see Supported languages and tools.
External network access:: You can access external network locations with external network access. You can create secure access to specific network locations external to Snowflake, then use that access from within the handler code.
Logging and tracing:: You can record code activity by capturing log messages and trace events, storing the data in a database you can query later.

Security¶

Whether you choose to have a stored procedure run with caller’s rights or owner’s rights can impact the information it has access to and the tasks it may be allowed to perform. For more information, see Understanding caller’s rights and owner’s rights stored procedures.

Stored procedures share certain security concerns with user-defined functions (UDFs). For more information, see the following:

You can help a procedure’s handler code execute securely by following the best practices described in Security Practices for UDFs and Procedures
Ensure that sensitive information is concealed from users who should not have access to it. For more information, see Protecting Sensitive Information with Secure UDFs and Stored Procedures

Handler code deployment¶

When creating a procedure, you can specify its handler – which implements the procedure’s logic – as code in-line with the CREATE PROCEDURE statement or as code external to the statement, such as compiled code packaged and copied to a stage.

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