Snowflake Stored Procedures: SQL Scripting, JavaScript and Snowpark Handlers

1.What a stored procedure is for, and where its handler lives

A stored procedure lets you extend Snowflake with procedural code: branching, looping and other programming constructs that plain SQL statements lack. The usual reason to write one is to automate work that takes several database operations and runs often. The documentation's example is a cleanup job that deletes data older than a cutoff date from many tables. You put every DELETE inside one procedure and pass the cutoff date in as a parameter. Users then call that one procedure instead of having to remember each table name. A procedure can also build and run SQL dynamically.

Procedures have one more ability that matters for governance. A procedure can run with the privileges of the role that owns it, not the role that calls it. The owner can then let users perform specific operations they could not otherwise run, although owner's-rights procedures come with limitations.

The workflow is the same in every language. You write a handler, create the procedure with CREATE PROCEDURE, then run it with CALL. A procedure can return a single value or, if the handler language supports it, tabular data. Languages differ in one practical way: where the handler code can be stored.

You don't always need a permanent procedure. A temporary procedure, created with CREATE PROCEDURE ... TEMPORARY or through the Snowpark API for Java, Python or Scala, lasts only for the current session. An anonymous procedure is created and called in a single statement and is dropped right after it runs. Neither approach requires the CREATE PROCEDURE privilege.

Sources1

2.Snowflake Scripting procedures (LANGUAGE SQL)

To write a procedure in SQL, use CREATE PROCEDURE with LANGUAGE SQL and put a Snowflake Scripting block (BEGIN ... END) in the AS clause. In Snowflake CLI, the Classic Console, or the Python Connector's execute_stream and execute_string methods, you must wrap the body in string literal delimiters (' or $$). Anonymous procedures always need those delimiters. Snowflake recommends keeping the body under 100 KB.

CREATE OR REPLACE PROCEDURE output_message(message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
  RETURN message;
END;

In a Scripting expression such as an IF or a RETURN, you refer to an argument by its plain name. Inside an embedded SQL statement, you must bind it by putting a colon in front of the name. A procedure can also return a table: declare a RESULTSET and return it with RETURN TABLE(...).

CREATE OR REPLACE PROCEDURE find_invoice_by_id(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
DECLARE
  res RESULTSET DEFAULT (SELECT * FROM invoices WHERE id =  ? );
BEGIN
  RETURN TABLE(res);
END;

Scripting procedures accept both input and output arguments, declared as <arg_name> [ { IN | INPUT | OUT | OUTPUT } ] <arg_data_type>, with IN as the default. An IN argument becomes a variable you can change inside the body, but its final value never goes back to the caller. An OUT argument's final value is returned to the calling program, such as an anonymous block or another procedure. The rules: you can assign to an OUT argument only through a variable, and one variable can't serve two OUT arguments. OUT arguments can't have a DEFAULT, can't receive session variables, and can't be used in asynchronous child jobs. UDFs don't support OUT arguments. A procedure can take at most 500 arguments in total.

One more restriction: you can't overload a procedure by changing only the argument types. If test_overloading(a IN NUMBER) exists, creating test_overloading(a OUT NUMBER) fails because the procedure already exists.

Sources2

3.JavaScript procedures and the snowflake object

A JavaScript handler runs SQL through an API built from four objects. snowflake is always available without being declared, and it creates statements and executes SQL. A Statement runs a prepared statement and exposes its metadata. A ResultSet holds the rows a query returns. SfDate is the return type for TIMESTAMP_LTZ, TIMESTAMP_NTZ and TIMESTAMP_TZ values. A typical handler calls snowflake.createStatement({sqlText: ...}), executes it, then reads values with next() and getColumnValue().

CREATE PROCEDURE f(argument1 VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var local_variable1 = argument1;  // Incorrect
var local_variable2 = ARGUMENT1;  // Correct
$$;

The mismatch can make a procedure fail without an explicit error, so use uppercase argument names consistently. The JavaScript engine is also restricted. You can't call eval(), and you can't import third-party libraries, only the standard JavaScript library. The engine blocks system calls, so there is no network or disk access, and its memory is limited. Watch numeric precision too: JavaScript keeps integers exact only up to ±(2^53 − 1). If a large NUMBER loses precision, read it with getColumnValueAsString() and cast it back in SQL.

Sources3

4.Snowpark Python procedures: the Session is passed in

A Snowpark procedure works differently from a JavaScript one. You don't build SQL strings. The handler gets a Snowpark Session and works through the DataFrame API. In CREATE PROCEDURE you set LANGUAGE PYTHON, a RUNTIME_VERSION, the snowflake-snowpark-python package, and HANDLER, which names the function to call.

CREATE OR REPLACE PROCEDURE myproc(from_table STRING, to_table STRING, count INT)
  RETURNS STRING
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.12'
  PACKAGES = ('snowflake-snowpark-python')
  HANDLER = 'run'
as
$$
def run(session, from_table, to_table, count):
  session.table(from_table).limit(count).write.save_as_table(to_table)
  return "SUCCESS"
$$;

The Session parameter does not appear in the SQL signature, which declares only from_table, to_table and count. On every call, Snowflake creates a Session and passes it as the first argument. You cannot create one yourself. All other arguments and the return value use the Python types that map to Snowflake data types, and errors are caught with ordinary Python exception handling.

There are two operational details to know. The snowflake-snowpark-python library inside procedures is usually one version behind the public release, and you can check the available versions in information_schema.packages. Also, "fire and forget" doesn't work: if the handler starts an asynchronous child query (for example with DataFrame.collect_nowait) and that query is still running when the procedure finishes, the child job is canceled.

Sources4

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Any stored procedure can return values to its caller through OUT arguments.Why is that wrong?

   Only Snowflake Scripting (SQL) procedures support output arguments. JavaScript, Python, Java and Scala procedures do not, and neither do UDFs.

   Covered in Snowflake Scripting procedures (LANGUAGE SQL)

2. 2.In a JavaScript procedure, you refer to an argument using the same case you declared it with.Why is that wrong?

   Unquoted argument names are uppercased on the SQL side, but JavaScript is case-sensitive, so a lowercase reference doesn't find the argument and can fail silently.

   Covered in JavaScript procedures and the snowflake object