SQL & JavaScript Stored Procedures
Snowflake · Stored Procedures & UDFs
Theory
A stored procedure packages procedural logic, such as variables, loops, conditions, error handling and several SQL statements, behind one name you run with CALL. Procedures can be written in SQL (Snowflake Scripting, with BEGIN ... END blocks), JavaScript, Python, Java or Scala. They can run DDL and DML, build SQL dynamically, and return a single value or a table.
A procedure runs with owner's rights by default, meaning it uses the privileges of the role that owns it, or with caller's rights (EXECUTE AS CALLER) using the caller's privileges. For example, archive_orders(90) can copy orders older than 90 days to an archive table, delete them from the main table and return how many rows moved.
Why we use it
Some jobs need several steps and decisions that a single SQL statement cannot express. Procedures keep that logic inside Snowflake, callable from tasks or tools, and owner's rights let users perform controlled admin actions without broad privileges.
When to use it
Use procedures for multi-step jobs, admin automation and logic called by tasks. Use a UDF when you need a value inside a SELECT. Prefer Snowflake Scripting for SQL-heavy logic and Python for complex processing.
SQL Scripting Procedures
Snowflake Scripting adds IF, FOR, WHILE, and variables directly to SQL, letting a procedure branch and loop without leaving the SQL language at all — the simplest option for most procedural logic.
JavaScript Procedures
For logic that's awkward in SQL Scripting, a JavaScript procedure runs inside Snowflake and calls back into SQL via a statement API, useful for dynamic SQL generation or complex string/JSON manipulation.
Calling and Returning Values
CALL invokes a procedure like a function call; procedures can return a scalar, a table, or nothing at all, and can perform DDL/DML with the caller's (or the procedure owner's) privileges depending on how it's defined.
How it works
- CALL archive_orders(90)
- Is it EXECUTE AS OWNER? Yes: Run with the owner role's privileges. No: Run with the caller's privileges.
- Execute statements, loops and conditions in order
- Did a statement raise an error? Yes: Handle it in an EXCEPTION block or fail the call. No: Continue.
- Return a value or table to the caller
Example
CREATE OR REPLACE PROCEDURE archive_orders(p_days_old INT)
RETURNS VARCHAR LANGUAGE SQL
AS
BEGIN
LET v_count INT := 0;
INSERT INTO orders_final SELECT * FROM orders WHERE order_date < DATEADD(DAY,-:p_days_old,CURRENT_DATE());
v_count := SQLROWCOUNT;
DELETE FROM orders WHERE order_date < DATEADD(DAY,-:p_days_old,CURRENT_DATE());
RETURN 'Archived ' || :v_count || ' orders';
END;
CALL archive_orders(90);Key points
- Snowflake Scripting covers most procedural needs without leaving SQL
- JavaScript and Python suit dynamic SQL or complex string and JSON logic
- Procedures run with owner's rights by default; EXECUTE AS CALLER changes that
- A procedure is invoked with CALL and cannot be used inside a SELECT
- Tasks often CALL procedures to run multi-step jobs