Stored Procedures - Part I

1. Stored Procedures - Part I

Now we get to learn about Stored Procedures! Stored Procedures are similar to UDFs in many ways, but there are big differences, and I think they’re great to study in succession because learning about the one illuminates the other. So I’m excited to dig in. We’ll first spend some time discussing what stored procedures do and how they differ from UDFs, but we’ll quickly move into the mechanics of creating + running them and we’ll spend the bulk of our time there. The reason for this is that, as you’ll see, stored procedures are so flexible that trying to enumerate their use cases is like trying to describe everything you can do with a pair of scissors – Here we’ll focus more on the tactical question of “how do you operate the scissors” than on all the things scissors can or cannot do. But we’ll cover a few common use cases. So what are stored procedures, and how do they differ from UDFs? Let’s start by slowing down – I heard the phrase “stored procedure” many, many times before I actually paused to think about the two parts of the name: One, it’s a procedure, so something that takes a series of logical steps, and Two, it’s stored, so you can reuse it. Okay, so you’re writing a block of code that does stuff, and saving it so you can call it again and again and again. What kinds of actions can you take with a stored procedure? You can run Data Definition Language commands ([DDL](https://docs.snowflake.com/en/sql-reference/sql-ddl-summary) commands), like ALTER, CREATE, DROP, SHOW, and USE. So if you wanted to procedurally create tables, you could do that; if you wanted to drop a schema, you could do that; if you wanted to switch to a different database or a different warehouse, you could do that. You can’t do that with UDFs. You can also run Data Manipulation Language ([DML](https://docs.snowflake.com/en/sql-reference/sql-dml)) commands, like INSERT or COPY INTO. You can’t do that with UDFs. Like UDFs, you can run SQL queries that perform calculations, but unlike UDFs, Stored Procedures aren’t *required* to return a value – they can, but they don’t have to. Like UDFs, you can write the internals of your Stored Procedure in [multiple languages](https://docs.snowflake.com/en/developer-guide/stored-procedures-vs-udfs) – Python, Java, Javascript, Scala, or SQL (though with stored procedures, this is an enhanced version of SQL called Snowflake Scripting which also lets you use loops, if-else-style branching, and more). At this point you might be wondering: Why even have UDFs if Stored Procedures can do what UDFs do, and then some? Well, Stored Procedures do in fact have some limitations UDFs don’t have. One downside of stored procedures is that they have to be called as independent statements – We’ll talk more about this in a second, but you have to run a separate CALL command for each stored procedure, whereas with UDFs, you can use many of them inside one SELECT statement. Another is that if you’re in a SQL worksheet, say, you can’t save the results of a Stored Procedure as a variable to then work with later. You *can* do this with UDFs. Okay, enough talking – Let’s turn to mechanics. Before we create our own stored procedure, I think it’s helpful to see that Snowflake already has some procedures ready for you. By this point in the course, you can probably guess that we’ll use a SHOW command to do this – In this case, SHOW PROCEDURES. Let’s run that: SHOW PROCEDURES; I’m seeing four total, and we can see that all four have a “yes” for “is_builtin,” as we’d expected. Cool, so we’re not starting from a blank slate! This SYSTEM$SEND_EMAIL is pretty neat – you use it to send an email notification to an address of your choosing, something you might want to do automatically if, say, an important process fails. You can already see that a procedure like this is quite different from a UDF – the goal here is to take an action, instead of performing a calculation and returning a value. So that’s it for “Stored Procedures - Part I.” We learned what a stored procedure is, how a UDF differs from a stored procedure, and how to see existing procedures with SHOW PROCEDURES. But things are about to get much, much cooler because in the next video, “Stored Procedures - Part II,” we’re going to make a stored procedure of our very own.

2. Let's practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.