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 important comparison between Snowflake stored procedures and user defined functions (UDFs).
Calling Methods:
Return Type:
Database Access:
How to choose Snowflake Stored Procedure or a UDFs ?
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.
Please visit below link to know more about Stored procedure and UDFs
Stored Procedure — How to write stored procedure in snowflake? | by Alexander | Aug, 2021 | Medium
UDFs — User Defined Functions (UDFs) in Snowflake — Part 1 | by Alexander | Sep, 2021 | Medium