If Statements Within Scalar Functions From T-SQL to Snowflake
by Andrés Campos, on Sep 8, 2022 3:00:00 AM
Whenever you migrate SQL Server IF statements that are found within scalar functions using BlackDiamond Studio, you will soon find out that they require special handling to make them work with the restrictions Snowflake has on this specific type of function. In fact, as was discussed in a previous article, a function with an SQL body can only support one SELECT statement.
Take for instance the following function:
As was discussed in a previous blog post, the DECLARE statements without any assignment will be removed by SnowConvert for SQL Server since they provide nothing for the current scope. The SELECT statement following them will be kept the same way it is, but it will be contained inside a Common Table Expression(CTE). Finally, the IF statements will be handled as a SELECT CASE EXPRESSION, where the conditionals for the IF, ELSE, or ELSE IF statements will be found inside the WHEN expression and the SET statements will become part of the THEN expression.
You can see the result in Snowflake Scripting here:
This can be considered a basic scenario for an IF since its only working with one statement inside the body of both the IF and ELSE. An IF statement can become extremely complex and difficult to transform once multiple statements are introduced inside its body.
The Nested Statements are also required to become a single query that will become part of the Common Table Expression for that IF to preserve the execution order for all the members found inside the body.
Let’s analyze the following input and output code from an IF statement found inside a scalar function using SnowConvert for SQL Server.
At first glance you will notice that the order in which the statements appear seems to be inverted. This is to emulate the behavior of an IF block and make sure the order in which the statements are executed is the correct one. Since the more nested a statement is inside the SELECT that will be ran first, this provides a single query that can fit into the CTE for that CASE Expression.