How to write stored procedure in snowflake?

As we know, a stored procedure is a pre-compiled object of the database. It’s a group of SQL statements. In Snowflake, a stored procedure can be written in two ways. The first you can use SQL, and another you can use JavaScript. I know you are wondering how we can use Java script in a stored procedure. Snowflake provides a way to use JavaScript inside the stored procedure. Here we are discussing this functionality only.

Things to keep in mind while writing stored procedure in snowflake using Java Script API,

Step 1: Prepare query variable by assigning sql query

Step 2: var statement = snowflake.createStatement({sqlText : query, binds : [<input parameter>]})

Step 3: var resultset = statement.execute()

Important points to consider,

1. We should pass all input parameter names in Capital Case (Optional).
2. We should use these input parameter in Java Script in Capital Case (Mandatory).
3. The cause behind it is that SQL by default convert columns in Capital Case and same is not true for Java Script variable which are case sensitive.
4. We should bind input parameter with query or alternatively, we can input parameters with query.
5. To access each row from resultset, we need to call resultset.next() one time.

Syntax:

CREATE OR REPLCAE PROCEDURE <PROCEDURE_NAME> (<attribute1 datatype>,<attribute2 datatype>)
returns <datatype>
language javascript
as
$$

$$
;

Scenario 1:

How to fetch the record in a table using a stored procedure in Snowflake by passing start value and end value ?

Expected Output:

Table Name: TRIPS

By passing START_STATION_ID and END_STATION_ID as input to the table TRIPS and store set of result in an array.

Create a stored procedure like below. Here, I have used Javascript. The Javascript code should be between $$ ..$$

CREATE OR REPLACE PROCEDURE TRIPS_DETAIL1 (START_STATION_ID FLOAT,END_STATION_ID FLOAT)
returns Array
language javascript
as
$$
var query = ‘SELECT TRIPDURATION FROM TRIPS WHERE START_STATION_ID = ? AND END_STATION_ID = ?’;
var statement = snowflake.createStatement({sqlText : query, binds : [START_STATION_ID,END_STATION_ID]});
var resultSet = statement.execute();

var arr = [];

while (resultSet.next())
{
arr.push(resultSet.getColumnValue(1));
}

return arr;

$$
;

Call the stored procedure:

CALL TRIPS_DETAIL1(‘306’,’270')

The syntax is:

CALL stored_procedure_name(parameter_value)

Now, if you will see the below query, you will get your result.

Result set of TRIPS_DETAIL1 procedure in array list

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.