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.
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:
- You put parameters to the function functionname(parameters type)
- You call the function by writing call functionname(parameters).
- The function must return some value, even if it is just doing an update. Otherwise you will get the error NULL result in a non-nullable column. This is because the worksheet editor in Snowflake needs something to display, even if it’s a null (Nan) value.
- The basic procedure is to use execute() to run SQL code that you have stored in a string. Database programmers know that creates what is called a resultset. So, you need to pull the first returned value into scope by calling next(). There is a result set with a select statement, delete, insert, and even update—even though you would not expect those to return any values.
- If the SQL statement returns more than one row, like in a SELECT, you would use a while (rs.next()) to loop through the results.
For more tutorials like this, explore these resources: