Working with Snowflake Scripting Stored Procedures

Writing stored procedure is one of the import method to encapsulate your business logic using procedural language. Snowflake does support writing a stored procedure using JavaScript and recently they started supporting SQL scripting much similar to Oracle PLSQL. In this article, we will check how to write Snowflake scripting stored procedures. We will also look into some examples.

In general, Stored procedure combines the complex SQL business logic with procedural statements such as IF-ESLE, LOOP, etc.

Snowflake Stored Procedures Overview

Snowflake stored procedures are used to encapsulate business specific logics such as data migration, data validation and handle the exceptions in your data or custom exception handling. For example, suppose you are a DBA you want to archive a database table for a specified date range. You can write multiple SQL statements, each of which copy 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.

Stored procedures are used to encapsulate the logic. They help make code easier to maintain and re-use.

Snowflake Procedural Language

Snowflake uses SQL scripting and JavaScript as a procedural language. It provides the control structures – branching and looping. Snowflake scripting also allows you to execute SQL statements such as dynamic SQL queries within stored procedure or an anonymous block.

You can write stored procedure using;

Snowflake Scripting
JavaScript API
Java (Snowpark)
Python (Snowpark)
Scala (Snowpark)

In this article we will check how to write stored procedure with the help of Snowflake scripting.

Snowflake Scripting Stored Procedure Syntax

Following is the Snowflake Scripting stored procedure syntax.

create or replace procedure myprocedure(param1 datatype1, param2 datatype2, ...)
  returns varchar
  language sql
  as
  $$
    -- Snowflake Scripting code
    declare
      variable datatype;
    begin
      Procedure_body;
      return something;
    end;
  $$;

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 Scripting Stored Procedure Example

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

create or replace procedure proc_test(param1 int, param2 int)
  returns INT
  language sql
  as
  $$
    -- Snowflake Scripting code
    begin     
      return case when param1 > param2 then param1 else param2 end;
    end;
  $$;

Overloading Snowflake Scripting Stored Procedure

Snowflake scripting stored procedures support overloading. That means that two procedures can have the same name if they have a different number of parameters or different data types for their parameters.