Bird
Raised Fist0
SnowflakeComparisonBeginner · 4 min read

Stored Procedure vs UDF in Snowflake: Key Differences and Usage

In Snowflake, a 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.

FactorStored ProcedureUser Defined Function (UDF)
PurposeComplex logic, control flow, data manipulationReusable computations inside SQL queries
Language SupportJavaScript or SQLSQL or JavaScript (scalar or tabular)
ExecutionCalled explicitly, can perform transactionsCalled within SQL expressions, no side effects
Side EffectsAllowed (e.g., insert/update/delete)Not allowed
Return TypeCan return complex results or noneReturns a value or table
Use CaseBatch processing, complex workflowsCalculations, 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.

sql
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:

sql
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);
Output
25
🎯

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.

✅

Key Takeaways

Stored Procedures handle complex logic and data changes with control flow.
UDFs provide reusable computations inside SQL queries without side effects.
Use Stored Procedures for workflows and data manipulation tasks.
Use UDFs for custom calculations and transformations in queries.
Stored Procedures support JavaScript and SQL; UDFs support SQL and JavaScript.