Stored procedures

Stored Procedures in Snowflake - Complete Guide for SnowPro Core Certification

What are Stored Procedures?

Stored procedures in Snowflake are named blocks of code that can be called to perform specific tasks. They are written using JavaScript, Snowflake Scripting (SQL), Python, Java, or Scala and are stored in the database for repeated execution. Stored procedures allow you to encapsulate business logic, perform complex operations, and execute multiple SQL statements as a single unit.

Why are Stored Procedures Important?

1. Code Reusability: Write once, execute multiple times across different sessions and users
2. Security: Execute with caller's rights or owner's rights, enabling controlled access to data
3. Complex Logic: Implement branching, looping, and error handling that pure SQL cannot achieve
4. Administrative Tasks: Automate maintenance operations like data loading, cleanup, and monitoring
5. Transaction Control: Manage commits and rollbacks within procedural code

How Stored Procedures Work in Snowflake

Creating a Stored Procedure:

CREATE OR REPLACE PROCEDURE procedure_name(parameter1 DATA_TYPE, parameter2 DATA_TYPE)
RETURNS return_type
LANGUAGE language_name
EXECUTE AS CALLER | OWNER
AS
$$
-- procedure body
$$;

Key Components:

• Parameters: Input values passed to the procedure
• Return Type: The data type of the value returned (can be a single value or a table)
• Language: JavaScript, SQL (Snowflake Scripting), Python, Java, or Scala
• Execute As: Determines the privilege context (CALLER or OWNER)

Caller's Rights vs Owner's Rights:

• EXECUTE AS CALLER: Procedure runs with the privileges of the user calling it. The caller must have access to all objects referenced.
• EXECUTE AS OWNER: Procedure runs with the privileges of the procedure owner. Useful for granting temporary elevated access.

Snowflake Scripting (SQL) Example:

CREATE PROCEDURE get_row_count(table_name VARCHAR)
RETURNS INTEGER
LANGUAGE SQL
AS
$$
DECLARE
row_count INTEGER;
BEGIN
SELECT COUNT(*) INTO row_count FROM IDENTIFIER(:table_name);
RETURN row_count;
END;
$$;

JavaScript Example:

CREATE PROCEDURE js_example()
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
var result = snowflake.execute({sqlText: 'SELECT CURRENT_DATE()'});
result.next();
return result.getColumnValue(1);
$$;

Calling Stored Procedures:

CALL procedure_name(argument1, argument2);

Key Features to Remember:

• Stored procedures can execute DDL and DML statements
• They support transaction control (BEGIN, COMMIT, ROLLBACK)
• Variables can be declared and used within procedures
• Error handling with TRY-CATCH blocks is supported
• Procedures can call other procedures
• Anonymous blocks allow running procedural code on-the-fly

Exam Tips: Answering Questions on Stored Procedures

1. Know the Languages: Memorize that Snowflake supports JavaScript, SQL (Snowflake Scripting), Python, Java, and Scala for stored procedures

2. EXECUTE AS Understanding: This is frequently tested. Remember CALLER means the invoker's privileges apply; OWNER means the creator's privileges apply

3. Stored Procedures vs UDFs: Procedures can return a single value or nothing and can contain multiple SQL statements including DDL. UDFs must return a value and are used in SQL expressions

4. Transaction Scope: Stored procedures can manage their own transactions with COMMIT and ROLLBACK

5. CALL Statement: Remember that procedures are invoked using CALL, not SELECT

6. Anonymous Blocks: Know that you can execute procedural code with EXECUTE IMMEDIATE or BEGIN...END blocks

7. Watch for Trick Questions: Procedures are different from functions - procedures use CALL, functions can be used in SELECT statements

8. Privileges: To create a procedure, you need USAGE on the database and schema, plus CREATE PROCEDURE privilege