Overview of Stored Procedures

Stored procedures are loosely similar to functions; they help make code easier to maintain and re-use.

As with functions, a stored procedure is created once and can be executed many times. A stored procedure is created with a CREATE PROCEDURE command and is executed with a CALL command.

A stored procedure returns a single value. Although you can run SELECT statements inside a stored procedure, the results must be used within the stored procedure, or be narrowed to a single value to be returned.

Snowflake stored procedures use JavaScript and, in most cases, SQL:

  • JavaScript provides the control structures (branching and looping).

  • SQL is executed by calling functions in a JavaScript API.

In this Topic:

Benefits of Stored Procedures

The benefits of stored procedures include:

  • Stored procedures allow procedural logic (branching and looping) and error handling, which straight SQL does not support.

  • Stored procedures enable you to dynamically create a SQL statement and execute it.

  • Stored procedures allow you to write code that executes with the privileges of the role that owns the procedure, rather than with the privileges of the role that runs the procedure. This allows you to delegate the power to perform specified operations to users who otherwise could not do so. However, there are some limitations on these owner’s rights stored procedures.

One common use for stored procedures is to help automate a task that requires multiple SQL statements and is performed frequently. For example, suppose that you want to clean up a database by deleting data older than a specified date. You can write multiple DELETE statements, each of which deletes data from one specific table. You can put all of those statements in a single stored procedure and pass a parameter that specifies the cut-off date. Then you can clean up the database simply by calling the stored procedure. As your database changes, you can update the procedure to clean up additional tables; if there are multiple users who use the cleanup command, they can call the procedure, rather than remember every table name and clean up each table individually.

Differences Between Stored Procedures and UDFs

Both stored procedures and UDFs (user-defined functions) make it easier to write modular code. However, there are important differences between UDFs and stored procedures.

Stored Procedures Are Called as Independent Statements

A stored procedure is called as an independent statement, rather than as part of a statement. The code below illustrates the difference between calling a stored procedure and calling a function:

CALL MyStoredProcedure1(argument_1);             -- Stored Procedure call

SELECT MyFunction1(argument_1) FROM table1;      -- Function call

Returning a Value from a Stored Procedure is Optional

A stored procedure is allowed to return a value, but is not required to return a value.

A function, on the other hand, is required to return a value.

Values Returned by Stored Procedures Are Not Directly Usable in SQL

Although a stored procedure can return a value, the syntax of the CALL command does not provide a place to store the returned value or a way to operate on it or pass it to another operation. There is no way in SQL to construct a statement like:

y = stored_procedure1(x);                         -- Not allowed

However, there are indirect ways to use the return value of a stored procedure:

  • You can call the stored procedure inside another stored procedure; the JavaScript in the outer stored procedure can retrieve and store the output of the inner stored procedure. Remember, however, that the outer stored procedure (and each inner stored procedure) is still unable to return more than one value to its caller.

  • You can call the stored procedure and then call the RESULT_SCAN function and pass it the statement ID generated for the stored procedure.

  • If the stored procedure is a caller’s rights stored procedure, you can store a result set in a temporary table, and use the temporary table after returning from the stored procedure call.

  • Of course, you can also store results in a permanent table and use those results after returning from the stored procedure.

  • If the volume of data is not too large, you can store multiple rows and multiple columns in a VARIANT (for example, as a JSON value) and return that VARIANT.

Single Stored Procedure per CALL Statement

You can call only one stored procedure per CALL statement.

You cannot use a stored procedure call as part of an expression.

However, inside a stored procedure, the stored procedure can call another stored procedure, or call itself recursively. An example of this is shown in the code examples section Examples.

For more details about calling a stored procedure, see Calling a Stored Procedure.

Stored Procedures Can Access the Database and Issue Nested Queries via an API

Snowflake provides a JavaScript API (in the form of JavaScript objects and methods). The API enables stored procedures to execute database operations such as SELECT, UPDATE, and CREATE.

The example below shows how a stored procedure can create and execute a SQL statement that calls another stored procedure:

create procedure ...
  -- The "$$" indicates the beginning and end of the JavaScript code
  -- in the stored procedure
  $$
  // Create a Statement object that can call a stored procedure named
  // MY_PROCEDURE().
  stmt1 = snowflake.createStatement( { sqlText: "call MY_PROCEDURE(22)" } );
  // Execute the SQL command; in other words, call MY_PROCEDURE(22).
  stmt1.execute();
  // Create a Statement object that executes a SQL command that includes
  // a call to a UDF.
  stmt2 = snowflake.createStatement( { sqlText: "select MY_UDF(column1) from table1" } );
  // Execute the SQL statement and store the output (the "result set") in
  // a variable named "rs", which we can access later.
  rs = stmt2.execute();
  // etc.
  $$;

JavaScript UDFs, unlike stored procedures, do not have access to an API that can perform database operations.

Choosing to Create a Stored Procedure or a UDF

In general, when deciding whether to create a stored procedure or UDF:

Create a stored procedure
  • If you are migrating an existing stored procedure from another application/system.

  • If you need to perform database operations:

    • Typical queries and DML, such as SELECT, UPDATE, etc.

    • Administrative tasks, including DDL such as deleting temporary tables, deleting data older than N days, or adding users.

Create a UDF
  • If you are migrating an existing UDF from another application/system.

  • If you need a function that can be called as part of a SQL statement and that must return a value that will be used in the statement.

  • If your output needs to include a value for every input row or every group. For example:

    select MyFunction(col1) from table1;
    
    select MyAggregateFunction(col1) from table1 group by col2;
    

Migration Considerations

Many database systems implement stored procedures purely in SQL. However, Snowflake stored procedures use JavaScript. The JavaScript statements can execute SQL statements via the JavaScript Stored Procedures API.

To migrate a stored procedure from another database system to Snowflake, embed the SQL in JavaScript, as shown in the examples in the next topic.