Difference Between Snowflake Stored Procedure and UDFs – SP vs UDFs

  • Post author:
  • Post last modified:August 29, 2022
  • Post category:Snowflake
  • Reading time:7 mins read

You can use both stored procedure and user defined functions to encapsulate the business logic and achieve specific tasks. However, there are important differences between UDFs and stored procedures. In this article, we will check the difference between Snowflake stored Procedure and user defined functions (UDFs).

Difference Between Snowflake Stored Procedure and UDFs - SP vs UDFs

Difference Between Snowflake Stored Procedure and UDFs

The Snowflake stored procedure vs user defined function is an interesting comparison. Both are used to achieve certain functionalities that are not possible using built-in functions available in Snowflake.

Following are some import comparison between Snowflake stored procedures and user defined functions (UDFs).

Calling Methods

Stored Procedure
  • A stored procedure in Snowflake is called as an independent statement. You cannot call it within an independent SQL statement.

For Example,

Following CALL statement is used to execute stored procedure in Snowflake.

CALL MyTempStoredProcedure(argument1);
  • You can call only one stored procedure per CALL statement.
  • You cannot call a user stored procedure as a part of an expression.
User Defined Function
  • User defined function in Snowflake is called as a part of the SQL statement.

For example,

Following SQL statement execute user defined function.

SELECT MyTempFunction(argument1) FROM your_table;
  • You can call multiple UDFs in a SQL statement.
  • You can call a user defined functions as a part of an expression.

Return Type

Stored Procedure

A stored procedure is allowed to return a value, but is not required to return a value. Stored procedure in Snowflake may or may not return results.

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.

For example,

Following CALL statement is not allowed in Snowflake.

x = CALL MyTempStoredProcedure(argument1);
User Defined Function

Where as, a user defined function in Snowflake, on the other hand, is required to return a value.

You can directly use the udfs return value inside your SQL statements.

For example,

Following statement uses udf.

SELECT MyTempFunction(col1), col1 
FROM your_table;

Database Access

Stored Procedure

Snowflake provides a JavaScript API. The API enables stored procedures to execute database operations such as SELECT, UPDATE, and CREATE.

User Defined Function

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

Choosing to Create a Snowflake Stored Procedure or a UDF

As mentioned earlier, you can use either SP or UDF to perform certain tasks as per your requirements.

You can create a Snowflake stored procedure:

  • If you are migrating an existing stored procedure from another application or system.
  • If you are performing any database operations such as SELECT, DELETE, CREATE, DELETE

You can create a Snowflake user defined Functions:

  • If you are migrating an existing UDF from another application or system.
  • In case if you need to use a function as a part of the SQL statement or expression.
  • If your output needs to include a value for every input row or every group.

Related Articles,

Hope this helps 🙂

Tags: , , ,