Snowflake Stored Procedures, Syntax, Limitations and Examples

To support migration from other relational databases, Snowflake supports the stored procedures. In this article, we will check Snowflake stored procedures, syntax, usage and restrictions with some examples.

Stored procedure combines the complex SQL business logic with procedural statements.

Snowflake Stored Procedures

Overview

Snowflake stored procedures are used to encapsulate the data migration, data validation and business specific logic’s and same time handle the exceptions if any in your data or custom exception handling. For example, suppose that you want to clean up a database by deleting data older than a specified date. You can write multiple DELETE statements, each of which deletes data from one specific table. You can put all of those statements in a single stored procedure and pass a parameter that specifies the date.

Similar to user defined functions, stored procedures are used to encapsulate the logic. They help make code easier to maintain and re-use.

Snowflake Procedural Language

Snowflake uses JavaScript as a procedural language. It provides the control structures – branching and looping.

You can execute SQL statements such as dynamic SQL queries by calling functions in a JavaScript API.

Snowflake Stored Procedure Syntax

Following is the Snowflake SQL stored procedure syntax.

create or replace procedure procedure-name(parameter1 type, parameter2 type, ...)
  returns float not null
  language javascript
  as     
  $$  
    Procedure_body [JavaScript logic]
  return 0.0; 
  $$
  ;

You should specify the return type of the procedure. A stored procedure returns a single row that contains a single column. For example, return 0.0 by default.

Snowflake Stored Procedure Example

Following is the sample Snowflake stored procedure to return largest of two numbers.

create or replace procedure proc_test(n1 float, n2 float)
 returns float not null
 language javascript
 as 
 $$
	if (N1>N2) { return N1 } else { return N2}
 $$
 ;

Executing Snowflake Stored Procedure

You can use CALL command to execute stored procedures.

Syntax

You can use following syntax to execute stored procedure.

CALL stored_procedure_name(parameter1, parameter2, ..);

Now, execute proc_test stored procedure by passing two numbers as an argument.

For example,

call proc_test(1.2, 1.4);
+-----------+
| PROC_TEST |
|-----------|
|       1.4 |
+-----------+

Snowflake Stored Procedure Benefits

Following are the benefits of Snowflake stored procedures.

Support JavaScript API and allows you to easily migrate existing stored procedures. For example, use JavaScript for procedural logic.
Stored procedures allow procedural logic (branching and looping) and error handling.
Enable you to dynamically create a SQL statement and execute it. For example, dynamically generate SQL statements and execute them.
A Stored procedure can automate the frequently performed tasks. For example, clean up backup tables or delete historical data.
Combine SQL and JavaScript API.

Difference Between Snowflake Stored Procedure and UDFs – SP vs UDFs

Snowflake Stored Procedure Limitations

Following are the some of Snowflake stored procedure limitations.

Currently, Snowflake stored procedure does not support transaction control commands such as BEGIN, COMMIT and ROLLBACK. Stored procedure runs entirely within a single transaction.
JavaScript cannot refer the third-party libraries within stored procedures.
Currently, you can only nest up to 8 stored procedures.
Sometimes, calling too many stored procedures at the same time can cause a deadlock.