Data Transformation · Topic 4 of 14

Stored Procedures - Design, SQL Scripting and JavaScript

Design Snowflake stored procedures: procedures vs UDFs, owner and caller rights, Snowflake Scripting blocks, cursors, exceptions and JavaScript procedures.

  • 5 min
In a nutshell
  • Procedures run DDL and DML and are invoked with CALL; UDFs run inside queries and cannot change objects.
  • EXECUTE AS OWNER is the default; CALLER uses the caller's privileges and session context.
  • Snowflake Scripting: DECLARE, BEGIN, EXCEPTION, END; :var binds; cursors, RESULTSET, EXECUTE IMMEDIATE, RAISE.

What it is

A stored procedure is a named block of procedural code that runs SQL statements and other logic, such as loops, branches and error handling. You use it to orchestrate work: load steps, maintenance, dynamic DDL, or anything a single SQL statement cannot do.

Procedures can be written in SQL (Snowflake Scripting), JavaScript, Python, Java and Scala. This page covers design and the SQL and JavaScript languages; the next page covers Snowpark.

Task or userCALL my_procrunsProcedureloops, branches, SQL statementsexecutesDDL and DMLchanges tablesreturnsOne value or a table

How it works

Procedure or UDF?

Stored procedureUDF
Invoked withCALL proc(...) as its own statementInside a query, in SELECT, WHERE and so on
Can run DDL and DMLYesNo
ReturnsOne value, or a table with RETURNS TABLEOne value per row, or rows (UDTF)
Typical roleOrchestration and administrationRow level calculation inside queries
Privilege modelOwner's or caller's rightsNo such choice
Exam tip

"Needs to create a table, run several statements and handle errors" is a stored procedure. "Needs to calculate a value in every row of a SELECT" is a UDF.

Owner's rights and caller's rights

A procedure runs with one of two privilege models, set by EXECUTE AS.

EXECUTE AS OWNER (default)EXECUTE AS CALLER
Privileges usedThe procedure owner'sThe caller's
Caller needsUSAGE on the procedure onlyUSAGE on the procedure plus privileges on the objects it touches
Session variables and contextDoes not use the caller's session variablesCan read and set the caller's session variables and uses the caller's current database and schema
Good forGiving controlled access to a task, such as a limited admin actionGeneric utilities that act on what the caller can already see

Owner's rights is the safer way to let a low privilege role perform one defined action. Calling a procedure needs the USAGE privilege on it.

Snowflake Scripting

Snowflake Scripting is the SQL extension for procedural logic. A block has optional DECLARE, a BEGIN ... END body and an optional EXCEPTION section.

CREATE PROCEDURE load_day(d DATE)
  RETURNS VARCHAR
  LANGUAGE SQL
  EXECUTE AS CALLER
AS
DECLARE
  loaded INTEGER DEFAULT 0;
  bad_date EXCEPTION (-20001, 'Date is in the future');
BEGIN
  IF (d > CURRENT_DATE()) THEN
    RAISE bad_date;
  END IF;
  INSERT INTO sales_clean SELECT * FROM sales_raw WHERE sale_date = :d;
  loaded := SQLROWCOUNT;
  RETURN 'Loaded ' || loaded || ' rows';
EXCEPTION
  WHEN bad_date THEN
    RETURN SQLERRM;
  WHEN STATEMENT_ERROR THEN
    RETURN 'SQL failed: ' || SQLERRM;
END;

Key elements:

  • Variables. Declare in DECLARE, or inline with LET. Assign with :=. In a SQL statement, refer to a variable or argument with a colon prefix such as :d.
  • Control flow. IF, CASE, and the loops FOR, WHILE, REPEAT and LOOP, with BREAK and CONTINUE.
  • Cursors. DECLARE c CURSOR FOR SELECT ..., then OPEN, FETCH and CLOSE, or the shorthand FOR rec IN c DO ... END FOR.
  • RESULTSET. Holds a query result; RETURN TABLE(rs) returns it from a procedure declared RETURNS TABLE (...).
  • Dynamic SQL. EXECUTE IMMEDIATE runs a string as SQL, useful for object names that vary.
  • Exceptions. Built in STATEMENT_ERROR and EXPRESSION_ERROR, custom exceptions with RAISE, and the variables SQLCODE, SQLERRM and SQLSTATE inside the handler.
  • Row counts. SQLROWCOUNT gives rows affected by the last DML.
DECLAREvariables, cursorsthenBEGINstatements, loops, brancheson errorEXCEPTIONWHEN handlersthenENDRETURN value

An anonymous block runs without creating an object. To run a block as a one off procedure, use WITH name AS PROCEDURE (...) ... CALL name(...).

JavaScript stored procedures

A JavaScript procedure uses the built in snowflake object to run SQL. As in JavaScript UDFs, argument names are uppercase in the body.

CREATE PROCEDURE row_count(tbl VARCHAR)
  RETURNS FLOAT
  LANGUAGE JAVASCRIPT
AS $$
  var stmt = snowflake.createStatement({
    sqlText: "SELECT COUNT(*) FROM IDENTIFIER(?)",
    binds: [TBL]
  });
  var rs = stmt.execute();
  rs.next();
  return rs.getColumnValue(1);
$$;
  • snowflake.createStatement({sqlText, binds}) builds a statement and execute() returns a result set; snowflake.execute({sqlText}) does both in one call.
  • Walk results with next() and read with getColumnValue(n).
  • Use binds with ? placeholders rather than concatenating strings, to avoid SQL injection.
  • Handle errors with try and catch; the error object has code, message and state.
  • JavaScript has no packages; use Python or Java when libraries are needed.

Language choice

LanguageChoose it when
Snowflake ScriptingLogic is mostly SQL with loops and error handling
JavaScriptYou need string and object manipulation with no libraries
Python, Java, ScalaYou need Snowpark DataFrames or libraries

Common mistakes

Calling a procedure inside a SELECT. Procedures run with CALL as a statement of their own.

Granting wide object privileges instead of using owner's rights. Owner's rights lets a low privilege role run one defined action safely.

Expecting an owner's rights procedure to see the caller's session variables. It does not; use EXECUTE AS CALLER.

Forgetting the colon. Inside Snowflake Scripting SQL, d is a column name but :d is the variable.

Lowercase argument names in JavaScript. Write TBL, not tbl.

What to remember for the exam

  • Procedures run DDL and DML and are invoked with CALL; UDFs run inside queries and cannot change objects.
  • EXECUTE AS OWNER is the default; CALLER uses the caller's privileges and session context.
  • Snowflake Scripting: DECLARE, BEGIN, EXCEPTION, END; :var binds; cursors, RESULTSET, EXECUTE IMMEDIATE, RAISE.
  • JavaScript uses snowflake.createStatement, execute, next, getColumnValue, uppercase arguments and bind variables.

Check yourself

  • What is the default privilege model of a stored procedure?

    Owner's rights, so it runs with the privileges of the role that owns it.

  • How do you refer to a Snowflake Scripting variable inside a SQL statement?

    With a colon prefix, such as :my_var.

  • Which JavaScript object runs SQL inside a procedure?

    The built in snowflake object, through createStatement and execute.

Topic 4 of 14 in Data Transformation

Test yourself on Data Transformation

The full Snowflake DEA-C02 question bank has 709 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.