• tomcoffing

Snowflake Stored Procedures - SQL Control Structures

Updated: Aug 30


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.


https://www.youtube.com/watch?v=o7YGXfxrJOM&t=24s.


You can also see a video of the Nexus migrating tables from Teradata to Snowflake here:

https://www.youtube.com/watch?v=_9ABivdwJPk&t=3s


Snowflake Stored Procedures - SQL Control Structures


The picture below shows the Snowflake control structures that help perform logic for critical decisions based on data and are excellent at manipulating data in flexible and powerful ways.




The IF-THEN statements are a simple form of IF statements. If the condition is true, statements between THEN and END IF execute otherwise, statements following END IF execute.



The example below creates and calls a procedure with two input arguments and then uses the IF, ELSEIF, ELSE, and END IF logic.



The example below creates and calls a procedure with a single input argument. A CASE and ELSE statement evaluate the input argument. This CASE example refers to a valued case statement because the input argument immediately follows CASE. Therefore, the procedure only evaluates VAR1 for equality conditions.




The example below creates and calls a procedure with two input arguments. A searched CASE and ELSE statement evaluate the input arguments.



The picture below shows Tom Coffing in the University of Arizona wrestling room. Tom was 3rd in the NCAA and became the last wrestler from the University of Arizona to be an All-American for the university. All-Americans at the University of Arizona had their picture retired on the wrestling room wall Tom also won two regional Olympic trials in 1980, the year the USA boycotted the Olympics.




60 views0 comments