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
- Procedures run DDL and DML and are invoked with
CALL; UDFs run inside queries and cannot change objects. EXECUTE AS OWNERis the default;CALLERuses the caller's privileges and session context.- Snowflake Scripting:
DECLARE,BEGIN,EXCEPTION,END;:varbinds; 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.
How it works
Procedure or UDF?
| Stored procedure | UDF | |
|---|---|---|
| Invoked with | CALL proc(...) as its own statement | Inside a query, in SELECT, WHERE and so on |
| Can run DDL and DML | Yes | No |
| Returns | One value, or a table with RETURNS TABLE | One value per row, or rows (UDTF) |
| Typical role | Orchestration and administration | Row level calculation inside queries |
| Privilege model | Owner's or caller's rights | No such choice |
"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 used | The procedure owner's | The caller's |
| Caller needs | USAGE on the procedure only | USAGE on the procedure plus privileges on the objects it touches |
| Session variables and context | Does not use the caller's session variables | Can read and set the caller's session variables and uses the caller's current database and schema |
| Good for | Giving controlled access to a task, such as a limited admin action | Generic 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 withLET. 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 loopsFOR,WHILE,REPEATandLOOP, withBREAKandCONTINUE. - Cursors.
DECLARE c CURSOR FOR SELECT ..., thenOPEN,FETCHandCLOSE, or the shorthandFOR rec IN c DO ... END FOR. - RESULTSET. Holds a query result;
RETURN TABLE(rs)returns it from a procedure declaredRETURNS TABLE (...). - Dynamic SQL.
EXECUTE IMMEDIATEruns a string as SQL, useful for object names that vary. - Exceptions. Built in
STATEMENT_ERRORandEXPRESSION_ERROR, custom exceptions withRAISE, and the variablesSQLCODE,SQLERRMandSQLSTATEinside the handler. - Row counts.
SQLROWCOUNTgives rows affected by the last DML.
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 andexecute()returns a result set;snowflake.execute({sqlText})does both in one call.- Walk results with
next()and read withgetColumnValue(n). - Use
bindswith?placeholders rather than concatenating strings, to avoid SQL injection. - Handle errors with
tryandcatch; the error object hascode,messageandstate. - JavaScript has no packages; use Python or Java when libraries are needed.
Language choice
| Language | Choose it when |
|---|---|
| Snowflake Scripting | Logic is mostly SQL with loops and error handling |
| JavaScript | You need string and object manipulation with no libraries |
| Python, Java, Scala | You 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 OWNERis the default;CALLERuses the caller's privileges and session context.- Snowflake Scripting:
DECLARE,BEGIN,EXCEPTION,END;:varbinds; 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.