Snowflake Stored Procedures

 Stored Procedures

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 Stored Procedure Syntax:

Create or replace procedure proc_name(param1 type,param2 type,…)

Returns float not null

Language javascript

As

$$

Procedure body [javascript logic]

Return 0.0;

$$;

Example:

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:

Using CALL command to execute stored procedures.

Syntax:

Call stored_procedure name (param1, param2,…);

Ex:

Call proc_test(1.2, 1.4);

Result: 1.4

Snowflake Stored Procedure Benefits:

·       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.

·       A Stored procedure can automate the frequently performed tasks. For example, clean up backup tables or delete historical data.

·       Combine SQL and JavaScript API.

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.