Snowflake Stored Procedures - Looping
Information from this blog comes from the Snowflake Architecture and SQL book by Tom Coffing and David Cook.
Are you ready to learn stored procedures from the best technical trainer the world has ever seen? Tom Coffing, AKA Tera-Tom, makes learning fun, exciting, and easy.
The picture above shows Tom Coffing teaching Teradata classes in the 1990s.
Tom became famous as a teacher because, for over 30 years, he memorized every student's name when he greeted them. Tom has taught over 1,000 classes and never missed a single name. Ask anyone who attended a class taught by Tom Coffing.
Tom has also led a team of developers for almost 20 years to create the Nexus, which queries, migrates, and joins data across all systems. The chart below shows which systems users can query, migrate, and join data automatically with Nexus. Many large customers moving from legacy systems to Snowflake use the Nexus Server from Coffing Data Warehousing.
You can download a free trial of Nexus at CoffingDW.com. You can also see some of the great features of Nexus right here.
You can also see a video of the Nexus migrating tables from Teradata to Snowflake here:
Snowflake Stored Procedures - Looping
The picture below shows the Snowflake procedure named INSERT_5_PROC, which uses a LOOP to insert five rows into a table.
Notice that the LANGUAGE is SQL, we have no input parameters, but we do a DECLARE on a variable we call COUNTER, which we set to a count of 0.
Notice we have a DROP TABLE if exists statement, which will attempt to drop the table, but if it does not exist, it won't cause an error that stops the procedure.
We create a table called MY_INSERT_5_TABLE, and it only has two columns: cntr and thetime.
Notice the keyword LOOP and END LOOP. Everything in between will continue to loop until it reaches the BREAK and then leaves the loop. The BREAK only happens if the COUNTER variable (which we increase with each loop iteration) is greater than five.
The procedure below inserts five rows into our table, including the value of our variable COUNTER and the CURRENT_TIMESTAMP.
The next example also inserts five rows into a table, but we use a WHILE loop. Notice the WHILE(COUNTER < 5) DO verbiage, which tells the procedure to continue to loop until the counter reaches five. Within each loop, we insert a row into the table containing the COUNTER value and the current timestamp from the Snowflake system. We also increment the value of the COUNTER by one within each loop.
After we create the procedure and call the procedure, we SELECT from the procedure, which contains five rows.
Our previous examples perform looping through a LOOP or WHILE command, but the example below is considered a FOR loop. What is interesting here is that we have an input parameter on the first line named ITERATION_LIMIT. Notice that when we CALL our procedure, we place a ten inside the parenthesis, which tells the procedure to loop ten times. The ten tells the procedure to loop ten times because of the FOR statement, which says FOR i in 1 to ITERATION_LIMIT DO, so the look knows to loop exactly ten times.
We could have specifically stated FOR i in 1 to 10 DO. Still, the beauty of a stored procedure with an input parameter is that we can create the procedure once but CALL the procedure many times and put in a different value for the input parameter as we see fit.
The example below creates and calls a procedure with a single input argument named REVERSE_ITERATION_LIMIT. We are doing another FOR loop, but it is in reverse, which means it will loop 20 times but count down backward.
Here is a workshop I give students in the classroom. My answer may surprise you because I will only perform one loop. Below, we ask each student to create a table named INSERT_1000_ROWS, which has two columns (CNTR1 and CNTR2).
The mission is to create and call a stored procedure that inserts 1,000 rows inside the table. However, the CNTR1 value must have 1,000 unique values (i.e., 1, 2, 3 to 1000). The second column (CNTR2) must have only 250 values repeated four times.
Notice in our example below that we perform our LOOP command and only break once the COUNTER exceeds 1000. Also, notice that we only increment our COUNTER but perform a modulo for COUNTER2 when we set COUNTER2 := mod(COUNTER, 250). The modulo divides the current COUNTER by 250 and delivers the remainder, so our COUNTER2 will have values ranging from 0 to 249.
The picture below shows Tom Coffing and his mom Sandy when Tom was inducted into his high school hall of fame. Tom was the first person to place in the state championships in any sport at Lakota High School. Tom placed 4th, 3rd, and second in the state during his sophomore, junior, and senior years.