Building stored procedures in SQL scripting, JavaScript, and Snowpark

Stored procedures in Snowflake execute procedural logic (control flow, loops, error handling) and can be written in JavaScript, SQL Scripting (Snowflake Scripting), Python, Java, or Scala. Unlike UDFs, stored procedures can perform DDL/DML side effects, run with caller's or owner's rights, and return a single value rather than being usable inline in a SELECT.

1 · Learn the must-know

• 1Stored procedures can execute DDL and DML statements and produce side effects, whereas UDFs cannot—this is the key distinguishing use case for choosing a procedure.
• 2EXECUTE AS CALLER runs the procedure with the invoking user's privileges, while EXECUTE AS OWNER (the default) runs with the privileges of the procedure's owner, which matters for security and privilege-escalation design.
• 3Snowflake Scripting (BEGIN...END blocks with DECLARE, variables, loops, IF/CASE, cursors, and RESULTSET) allows procedural SQL logic to be written natively without JavaScript.
• 4Python and Java stored procedures use the Snowpark API, requiring a specified RUNTIME_VERSION, HANDLER, and PACKAGES/IMPORTS for external libraries staged in Snowflake.
• 5Stored procedures can call other stored procedures and can return tabular results via CALL ... using RESULTSET or by returning a TABLE type, but they are invoked with CALL, not SELECT.
• 6Exception handling in Snowflake Scripting uses EXCEPTION blocks with system-defined or user-defined exceptions (via RAISE), enabling structured error handling within transformation pipelines.

2 · Check your understanding