Overview of Stored Procedures

Stored procedures are loosely similar to functions. 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

Stored procedures allow:

  • Procedural logic (branching and looping), which straight SQL does not support.

  • Error handling.

  • Dynamically creating a SQL statement and execute it.

  • Writing 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 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.

One common use for stored procedures is to 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 simply call the procedure 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 cleanup command, they can call one 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(column_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

The value returned by a stored procedure, unlike the value returned by a function, cannot be used directly 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 the value 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.

  • You can store a result set in a temporary table or permanent table, and use that table after returning from the stored procedure call.

  • 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

A single executable statement can call only one stored procedure. In contrast, a single SQL statement can call multiple functions.

Similarly, a stored procedure, unlike a function, cannot be called 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. The $$ indicates the beginning and end of the JavaScript code in the stored procedure.

create procedure ...
  $$
  // Create a Statement object that can call a stored procedure named
  // MY_PROCEDURE().
  var 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.
  var 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.
  var rs = stmt2.execute();
  // etc.
  $$;

Unlike stored procedures, UDFs (including JavaScript UDFs) 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. To migrate a SQL stored procedure to Snowflake, embed the SQL in JavaScript, as shown in the examples in Working with Stored Procedures.