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

  1. CALL archive_orders(90)
  2. Is it EXECUTE AS OWNER? Yes: Run with the owner role's privileges. No: Run with the caller's privileges.
  3. Execute statements, loops and conditions in order
  4. Did a statement raise an error? Yes: Handle it in an EXCEPTION block or fail the call. No: Continue.
  5. 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 topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic