What is a Stored Procedure in Snowflake?
A stored procedure in Snowflake is a reusable block of SQL and procedural logic that can be executed as a single unit. Stored procedures help automate tasks such as data transformations, batch processing, and administrative operations.
Key Features of Snowflake Stored Procedures:
- Encapsulation of Business Logic – Store and execute complex logic within the database.
- Supports JavaScript – Write procedural code using JavaScript for greater flexibility.
- Parameterized Execution – Accept input parameters for dynamic behavior.
- Transaction Control – Perform multi-step operations with rollback capabilities.
How to Create a Stored Procedure in Snowflake
Stored procedures in Snowflake are typically written using SQL and JavaScript. Here’s a basic example:
CREATE OR REPLACE PROCEDURE my_procedure()
RETURNS STRING
LANGUAGE JAVASCRIPT
AS $$
return 'Hello from my procedure!';
$$;How to Execute a Stored Procedure in Snowflake
You can execute a stored procedure using the CALL statement:
CALL my_procedure();How to View Stored Procedures in Snowflake
To list stored procedures in a schema, use:
SHOW PROCEDURES IN my_database.my_schema;To view the details of a specific stored procedure:
DESCRIBE PROCEDURE my_procedure();How to Call a Stored Procedure in Snowflake
Stored procedures are executed using the CALL command, and they can accept parameters:
CALL my_procedure('parameter_value');How to Find a Stored Procedure in Snowflake
You can search for stored procedures using the INFORMATION_SCHEMA:
SELECT * FROM INFORMATION_SCHEMA.PROCEDURES WHERE PROCEDURE_NAME = 'my_procedure';How to Drop a Stored Procedure in Snowflake
To delete a stored procedure, use the DROP PROCEDURE statement:
DROP PROCEDURE my_procedure();How Does a Snowflake Stored Procedure Compare to a User-Defined Function (UDF)?
| Feature | Stored Procedure | User-Defined Function (UDF) |
|---|---|---|
| Purpose | Execute multi-step operations | Compute and return a single value |
| Supports Transactions | Yes | No |
| Returns Multiple Rows | Yes (via table operations) | No |
| Supports JavaScript | Yes | Yes |
| Execution | Called with CALL | Used in SELECT, WHERE, etc. |
Using Snowflake with Evidence
For teams working with Snowflake stored procedures, Evidence provides an efficient way to integrate Snowflake data into reports and dashboards. With Evidence, you can:
- Execute stored procedures to transform and aggregate data.
- Automate reporting workflows based on procedure outputs.
- Leverage version-controlled reporting for accurate insights.
Check out the Evidence documentation to learn more about using Snowflake stored procedures for analytics and reporting.
