Get startedGet started for free

Stored Procedures - Part II

1. Stored Procedures - Part II

In the last video we learned a bunch about stored procedures, and how they compare to UDFs. But now let’s get to the fun part – Making our own stored procedure. In our case, let’s work with some TastyBytes data we haven’t looked at before – the transaction data. This spans two tables – ORDER_HEADER, which has basic info about the order (one row per order), and ORDER_DETAILS, which has a row per item per order – so if someone ordered multiple items in a single order, each item would be in a different row. Here we’ll work with ORDER_HEADER, and let’s pretend that for some reason we didn’t want to store data older than 180 days, and every day we want to delete that older data. Stored Procedures are perfect for this kind of thing. Let’s take a look at the ORDER_HEADER table: SELECT * FROM FROSTBYTE_TASTY_BYTES_CLONE.RAW_POS.ORDER_HEADER LIMIT 100; You can see that it’s got the order id, the truck id, the location, and more. The column we care about most here is this ORDER_TS, which stands for order timestamp. Let’s check out the range of dates in the ORDER_HEADER table: SELECT MAX(ORDER_TS), MIN(ORDER_TS) FROM FROSTBYTE_TASTY_BYTES_CLONE.RAW_POS.ORDER_HEADER; Okay, so the earliest transaction happened on January 1st of 2019, and the most recent happened on November 1st of 2022. Let’s save this max order_ts value as a variable that we’ll call max_ts: SET max_ts \= (SELECT MAX(ORDER_TS) FROM FROSTBYTE_TASTY_BYTES_CLONE.RAW_POS.ORDER_HEADER); And if we run that: SELECT $max_ts; That looks good. Great, now let’s subtract 180 days from that max_ts variable, and save that as the cutoff timestamp. SELECT DATEADD('DAY',-180,$max_ts); That looks good to me. Okay, so let’s save that value as cutoff_ts: SET cutoff_ts \= (SELECT DATEADD('DAY',-180,$max_ts)); Awesome! Now we’re ready to write the SQL logic to pull all data older than 180 days, which we want to do because that’s the data we’re going to want to delete with a DELETE command inside the stored procedure we’ll make in a moment. Once we get this logic correct, making the stored procedure is easy. We could do a SELECT *, but that’s wasteful because it will pull a ton of data, so instead, let’s just pull the max date and make sure it looks like it’s 180 days in the past once the logic in the WHERE clause is applied. SELECT MAX(ORDER_TS) FROM FROSTBYTE_TASTY_BYTES_CLONE.RAW_POS.ORDER_HEADER WHERE ORDER_TS \< $cutoff_ts; If we run that, that looks like the right value! 180 days before the most recent timestamp. Great. Okay, so there’s one last thing we should cover before we make our Stored Procedure, and that’s what a Snowflake Scripting block is. As I mentioned before, Snowflake Scripting is like an enhanced version of SQL that lets you use SQL alongside additional kinds of logic, like if-else statements. One of the most common places to see Snowflake Scripting is inside a Stored Procedure, and we’re about to use a very simple Snowflake Scripting Block inside the Stored Procedure we’ll make. Here’s their structure: You can start with DECLARE, and then declare variables (this part is optional); then you have a BEGIN statement (this is not optional); then you can add exceptions in case an error occurs (this is optional); and finally you finish with an END statement. In what we’re about to make, we’ll use DECLARE, BEGIN, and END, but to keep things simple, we won’t add in any exceptions. One last thing to note – Within the DECLARE part, you just state the variable name and its type. And then when you use that variable after the BEGIN statement, you set its value with a :=, and you reference its value by sticking a colon in front of the variable name. This is a lot coming at you all at once, but it will make sense in a second. Cool, so now we get to actually create our procedure. We do this with the CREATE PROCEDURE command: CREATE OR REPLACE PROCEDURE delete_old() RETURNS BOOLEAN LANGUAGE SQL AS $$ DECLARE max_ts TIMESTAMP; cutoff_ts TIMESTAMP; BEGIN max_ts := (SELECT MAX(ORDER_TS) FROM FROSTBYTE_TASTY_BYTES_CLONE.RAW_POS.ORDER_HEADER); cutoff_ts := (SELECT DATEADD('DAY',-180,:max_ts)); DELETE FROM FROSTBYTE_TASTY_BYTES_CLONE.RAW_POS.ORDER_HEADER WHERE ORDER_TS \< :cutoff_ts; END; $$ ; This is a lot to digest, so let’s go slowly here. We created our procedure, and called it delete_old, and because we didn’t include any arguments inside the parentheses, that means this doesn’t use any arguments. You just call it, and it does its thing. We specify the return type, but you don’t need to actually return anything with a stored procedure, and you’ll notice that we didn’t include a return statement below in the body of the stored procedure. We declared our two variables, max_ts, and cutoff_ts, and stated they’re both timestamps. Then, after the BEGIN, we actually set the values of each of those, one after the other. Finally, we actually ran our DELETE statement, which follows the syntax of “DELETE FROM” and then you specify the table name, followed by the conditions of what you want to delete – in our case, any orders before the cutoff timestamp 180 days in the past. And then we end! Just to make sure that all worked just fine, lets run SHOW PROCEDURES: SHOW PROCEDURES; There it is! You’ll see that it’s not built-in – We made this one ourselves. Now let’s run the DESCRIBE PROCEDURE command, followed by the name of our stored procedure (with the parentheses!), to learn more about our stored procedure: DESCRIBE PROCEDURE delete_old(); This gives us a bunch of information – and we can see that everything looks like it’s in order. The return type is BOOLEAN (though that doesn’t matter here). The stored procedure was written in SQL. And if we double click on the body, we can see the stored procedure’s logic. Cool! Everything looks good. Now let’s finally run the thing! If we did this right, that should delete all the data from our orders table that’s more than 6 months from the most recent order. You run a stored procedure with the CALL command. So here, we do: CALL DELETE_OLD(); Awesome! It looks like that worked! Let’s double check by seeing the new minimum date in our ORDER_HEADER table: SELECT MIN(ORDER_TS) FROM FROSTBYTE_TASTY_BYTES_CLONE.RAW_POS.ORDER_HEADER; Great! If we compare that to cutoff_ts, we’ll see that we did it! SELECT $cutoff_ts; We’re champions. There are so many other cool things to learn about stored procedures – again, they’re like scissors, and there’s a lot you can do with them. For example, we didn’t cover writing Stored Procedures in Python or Javascript, or anything like that. But this isn’t a course where we lament what we didn’t cover – We focus on what we *did* cover, and we’ve covered a LOT! We’ve come a long way! I’m very proud of us. Here’s what we covered in this video alone: One, we learned about what stored procedures are and what languages they can be written in. Two, we learned how to list the available procedures with SHOW PROCEDURES. Three, we explained how UDFs and stored procedures differ. Four, we created a stored procedure. Five, we called a stored procedure. And six, we learned about our stored procedure using DESCRIBE PROCEDURE. Now when you see stored procedures, you don’t need to be intimidated! They’re just another tool in the toolkit.

2. Let's practice!

Create Your Free Account

or

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