Overview of Stored Procedures

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.

You can write a stored procedure in one of the following languages:

From a stored procedure, you can return a single value or tabular data.

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.

Returning a Value from a Stored Procedure is Optional

Both functions and stored procedures can return a value. However:

  • The purpose of a function is to calculate and return a value. A function always returns a value explicitly by specifying an expression (either with or without a keyword such as return, depending upon the language). For example, the body of a JavaScript UDF must have a return statement that returns a value.

  • The purpose of a stored procedure is generally to execute SQL statements (e.g. to perform administrative operations). The body of a stored procedure is allowed, but not required, to explicitly return a value (e.g. an error indicator).

    Note that every CREATE PROCEDURE statement must include a RETURNS clause that defines a return type, even if the procedure does not explicitly return anything.

    For example, in the following pseudo-code, the RETURNS clause is mandatory, but the RETURN statement is optional:

    CREATE PROCEDURE f()
    RETURNS INTEGER          -- required
    LANGUAGE SQL
    AS
    $$
        ...
        RETURN error_code;   -- optional
    $$
    ;
    

    If a procedure does not explicitly return a value, then it implicitly returns NULL.

Stored Procedures Are Called as Independent Statements

A function evaluates to a value, and can be used in contexts in which a general expression can be used (e.g. SELECT my_function() ...).

A stored procedure does not evaluate to a value, and cannot be used in all contexts in which a general expression can be used. For example, you cannot execute SELECT my_stored_procedure()....

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

CALL MyStoredProcedure_1(argument_1);

SELECT MyFunction_1(column_1) FROM table1;

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

Within a stored procedure, you can execute database operations, such as SELECT, UPDATE, and CREATE:

  • In a JavaScript stored procedure, you can use the JavaScript API (in the form of JavaScript objects and methods) to perform these operations.

    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.
      $$;
    
  • In a Snowflake Scripting stored procedure, you can execute SQL statements.

    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 Snowflake Scripting code in the stored procedure.

    CREATE PROCEDURE ...
      -- Call a stored procedure named my_procedure().
      CALL my_procedure(22);
      -- Execute a SQL statement that includes a call to a UDF.
      SELECT my_udf(column1) FROM table1;
    

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;
    
Back to top