Overview of Stored Procedures — Snowflake Documentation (2022)

.

Related Topics

  • Overview of UDFs

  • Protecting Sensitive Information with Secure UDFs and Stored Procedures

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

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

  • Java (using Snowpark)

  • JavaScript

  • Python (using Snowpark)

  • Scala (using Snowpark)

  • Snowflake Scripting

From a stored procedure, you can return a single value or (if you are using Snowflake Scripting) tabular data.

Note

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

In this Topic:

  • Benefits of Stored Procedures

  • Differences Between Stored Procedures and UDFs

    • Returning a Value from a Stored Procedure is Optional

    • Stored Procedures Are Called as Independent Statements

    • Values Returned by Stored Procedures Are Not Directly Usable in SQL

    • Single Stored Procedure per CALL Statement

    • Stored Procedures Can Access the Database and Issue Nested Queries

  • Choosing to Create a Stored Procedure or a UDF

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 privilegesof the role that owns the procedure, rather than with the privileges of therole that runs the procedure. This allows the stored procedure owner to delegate the power toperform specified operations to users who otherwise could not do so. However, there are limitationson these owner’s rights stored procedures.

One common use for stored procedures is to automate a task that requiresmultiple SQL statements and is performed frequently. For example,suppose that you want to clean up a database by deleting dataolder than a specified date. You can write multiple DELETE statements,each of which deletes data from one specific table. You can put allof those statements in a single stored procedure and pass aparameter that specifies the cut-off date. Then you can simply call theprocedure 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 thanremember 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 specifyingan 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 administrativeoperations). 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 proceduredoes 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 -- requiredLANGUAGE SQLAS$$ ... 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 storedprocedure 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 ofthe CALL command does not provide a place to store the returnedvalue 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 JavaScriptin the outer stored procedure can retrieve and store the output of theinner 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 itthe 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 thestored procedure call.

  • If the volume of data is not too large, you can store multiple rows and multiple columns in a VARIANT (forexample, 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 callmultiple 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 performthese operations.

    The example below shows how a stored procedure can create and execute a SQL statement that calls another storedprocedure. 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 storedprocedure. 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 canperform 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, oradding 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 mustreturn 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;

Top Articles

You might also like

Latest Posts

Article information

Author: Rev. Porsche Oberbrunner

Last Updated: 09/06/2022

Views: 5856

Rating: 4.2 / 5 (73 voted)

Reviews: 80% of readers found this page helpful

Author information

Name: Rev. Porsche Oberbrunner

Birthday: 1994-06-25

Address: Suite 153 582 Lubowitz Walks, Port Alfredoborough, IN 72879-2838

Phone: +128413562823324

Job: IT Strategist

Hobby: Video gaming, Basketball, Web surfing, Book restoration, Jogging, Shooting, Fishing

Introduction: My name is Rev. Porsche Oberbrunner, I am a zany, graceful, talented, witty, determined, shiny, enchanting person who loves writing and wants to share my knowledge and understanding with you.