Stored Procedure vs UDF in Snowflake: Key Differences and Usage
Stored Procedure is a set of SQL and procedural logic that can perform complex operations and control flow, while a User Defined Function (UDF) is a reusable function mainly for computations within SQL queries. Stored Procedures support multiple statements and side effects, whereas UDFs are designed for single expressions or scalar computations without side effects.Quick Comparison
This table summarizes the main differences between Stored Procedures and UDFs in Snowflake.
| Factor | Stored Procedure | User Defined Function (UDF) |
|---|---|---|
| Purpose | Complex logic, control flow, data manipulation | Reusable computations inside SQL queries |
| Language Support | JavaScript or SQL | SQL or JavaScript (scalar or tabular) |
| Execution | Called explicitly, can perform transactions | Called within SQL expressions, no side effects |
| Side Effects | Allowed (e.g., insert/update/delete) | Not allowed |
| Return Type | Can return complex results or none | Returns a value or table |
| Use Case | Batch processing, complex workflows | Calculations, data transformations in queries |
Key Differences
Stored Procedures in Snowflake are designed for executing complex logic that may include multiple SQL statements, control flow (loops, conditions), and can modify data or manage transactions. They are typically written in JavaScript or SQL and are called explicitly by users or applications.
On the other hand, User Defined Functions (UDFs) are meant to extend SQL by defining custom functions that can be used inside SQL queries. UDFs are usually scalar functions returning a single value or table functions returning rows, and they cannot cause side effects like data changes.
In summary, use Stored Procedures when you need procedural control and data manipulation beyond simple calculations, and use UDFs to encapsulate reusable expressions or computations within your SQL queries.
Code Comparison
Here is an example showing how to calculate the square of a number using a Stored Procedure in Snowflake.
CREATE OR REPLACE PROCEDURE square_number_sp(num FLOAT) RETURNS FLOAT LANGUAGE JAVASCRIPT AS $$ return num * num; $$; -- Call the procedure CALL square_number_sp(5);
UDF Equivalent
The equivalent calculation using a scalar UDF looks like this:
CREATE OR REPLACE FUNCTION square_number_udf(num FLOAT) RETURNS FLOAT LANGUAGE SQL AS $$ SELECT num * num $$; -- Use the function in a query SELECT square_number_udf(5);
When to Use Which
Choose Stored Procedures when you need to run complex workflows, multiple SQL statements, or operations that change data and require control flow like loops and conditions.
Choose UDFs when you want to create reusable functions for calculations or transformations that run inside SQL queries without side effects.
Stored Procedures are best for batch jobs and procedural tasks, while UDFs are ideal for extending SQL with custom computations.
