Choosing Whether to Write a Stored Procedure or a User-Defined Function¶

This topic describes key differences between stored procedures and UDFs, including differences in how each may be invoked and in what they may do.

At a high level, stored procedures and UDFs differ in how they are typically used, as described below.

Stored Procedure Purpose	User-Defined Function Purpose
Generally to perform administrative operations by executing SQL statements. The body of a stored procedure is allowed, but not required, to explicitly return a value (such as an error indicator).	Calculate and return a value. A function always returns a value explicitly by specifying an expression. For example, the body of a JavaScript UDF must have a `return` statement that returns a value.

When to Create a Stored Procedure or a UDF¶

In general, when deciding whether to create a stored procedure or UDF, consider the following recommendations:

Create a Stored Procedure When…	Create a UDF When…
You’re migrating an existing stored procedure from another application/system. 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.	You’re migrating an existing UDF from another application/system. 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. 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; Copy

Supported Handler Languages¶

When you write a procedure or UDF, you write its logic as a handler in one of the supported languages. The following table lists the supported languages.

Stored Procedures	User-Defined Functions
Java	Java
JavaScript	JavaScript
Python	Python
Scala	Scala
Snowflake Scripting	SQL

Usage and Behavior Differences¶

The following sections describe specific differences in the behaviors supported by procedures and UDFs.

UDFs Return a Value; Stored Procedures Need Not¶

Stored procedures and functions can both return a value. However:

The body of a stored procedure is allowed, but not required, to explicitly return a value (such as an error indicator). That’s because the purpose of a stored procedure generally is to perform administrative operations by executing SQL statements.

Note that every CREATE PROCEDURE statement must include a RETURNS clause that specifies a return type, even if the procedure does not explicitly return anything. If a procedure does not explicitly return a value, then it implicitly returns NULL.

Code in the following example declares a return type for the procedure with a RETURNS clause, but a value is only returned in the case of an error. In other words, not every code path returns a value.
```
create or replace procedure do_stuff(input number)
returns varchar
language sql
as
$$
declare
  error varchar default 'Bad input. Number must be less than 10.';

begin
  if (input > 10) then
    return error;
  end if;

  -- Perform an operation that doesn't return a value.

end;
$$
;
```
Copy
If a procedure does not explicitly return a value, then it implicitly returns NULL.
A UDF always returns a value explicitly by specifying an expression. This is because a UDF’s purpose is to calculate and return a value. For example, the body of a JavaScript UDF must have a return statement that returns a value.

UDF Return Values Are Directly Usable in SQL; Stored Procedure Return Values May Not Be¶

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 (for most handler languages) 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.

Copy

There are indirect ways to use the return value of a stored procedure:

You can call the stored procedure inside another stored procedure. For example, when the stored procedure handler is written in JavaScript, 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.

UDFs Can Be Called In the Context of Another Statement; Stored Procedures Are Called Independently¶

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 UDF evaluates to a value, and can be used in contexts in which a general expression can be used (such as SELECT my_function() ...).

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;

Copy

For more details about calling functions and procedures, see the following:

Multiple UDFs May Be Called With One Statement; a Single Stored Procedure Is Called With One 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 functions and procedures, see the following:

UDFs May Not Access the Database; Stored Procedures Can¶

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

For example, in a JavaScript stored procedure, you can use the JavaScript API 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 handler 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.
  $$;

Copy

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;
```
Copy

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