Using Stored Procedures in Snowflake

Snowflake supports stored procedures. Stored procedures let you write a series of commands and store them for later use.

(This article is part of our Snowflake Guide. Use the right-hand menu to navigate.)

When to use a stored procedure

We’ve previously covered user defined functions (UDFs), which you use on a column. A stored procedure runs by itself. In other words, it goes off and "does something," like update a table. That’s why stored procedures are good for batch-like actions.

You can also conditionally tie stored procedures to database events, not unlike what’s called a trigger in other database products.

Data engineers can make a data pipeline with stored procedures, too. (A subject we will explore in depth in following posts.)

JavaScript for stored procedures

Snowflake stored procedures must be written in JavaScript. It makes sense that you must use a programming language beside SQL, since SQL does not support variable assignment. You would create variables to run calculations, etc.

Don't worry if you don't know JavaScript—you can simply copy boilerplate code and put SQL into the proper location. The SQL is really the only part that varies, for the most part. So, there is not much you need to understand.

Snowflake JavaScript is bare bones JavaScript. It does not let you import libraries that are external to the language. So, you can create arrays, variables, simple objects and there is error handling. But you could not, for example, use math functions form the Math library

Line by line tutorial: Stored procedure

We will put one simple example here and explain each line of the code. (So, you don't need any sample data.)

Look at the function below. Note the following:

create or replace procedure setprice(ORDERNUMBER varchar(100))
    returns float 
    not null
    language javascript
    as
    $$
  
     
    sql_command = "update orders set price = 2 where ordernumber = " + ORDERNUMBER   ;
    
   
    var stmt = snowflake.createStatement(
           {
           sqlText: sql_command
           }
        );
    var res = stmt.execute();
    res.next()
    
    price = res.getColumnValue(1);
    return price;
     
  
    $$
    ;
 
 call setprice(489)

Additional resources

For more tutorials like this, explore these resources: