If Statements Within Scalar Functions From T-SQL to Snowflake

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.

IF/ELSE statements can be handled in a few different ways, but primarily, they can be either transformed to JavaScript or Snowflake Scripting. If they contain statements that cannot be supported inside one SELECT (such as WHILE statements or multiple nested IF Statements), the transformation to JavaScript would be preferred. If the statement is using the CASE EXPRESSION inside the SELECT allowing conditionals inside the queries, Snowflake Scripting would be the recommended approach. While the JavaScript transformation is straightforward ( although the CASE statement might not be so obvious at first glance), the Snowflake Scripting approach allows the expression to keep a similar format and preserve the  functionality and behavior it previously had on SQL Server.

Take for instance the following function:

CREATE OR ALTER FUNCTION PURCHASING.HasActiveFlag(@BusinessEntityID int)
RETURNS VARCHAR(10) AS
BEGIN
    DECLARE @result VARCHAR(10)
    DECLARE @ActiveFlag BIT
    
    SELECT @ActiveFlag = ActiveFlag from PURCHASING.VENDOR v where v.BUSINESSENTITYID = @BusinessEntityID
    
    IF @ActiveFlag = 1
        SET @result = 'YES'
    ELSE IF @ActiveFlag = 0 
        SET @result = 'NO'
    
    RETURN @result
END

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:

CREATE OR REPLACE FUNCTION PURCHASING.HasActiveFlag (
    BusinessEntityID int
)
RETURNS VARCHAR(10)
AS
$$
    WITH
    CTE1 as (SELECT ActiveFlag AS ActiveFlag 
        from PURCHASING.VENDOR v
        where v.BUSINESSENTITYID = BusinessEntityID),
    CTE2 as (SELECT CASE 
                WHEN (SELECT ActiveFlag from CTE1) = 1 THEN 'YES'
                WHEN (SELECT ActiveFlag from CTE1) = 0 THEN 'NO' 
                    end as result)
    SELECT result FROM CTE2
$$;

SELECT PURCHASING.HasActiveFlag(1516) as has_active_flag

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.

Input

IF @param1 = 'first'
    BEGIN
        select @Result = @cashAmount;
        declare @TaxedResult MONEY = @cashAmount + 35;
        SET @Result = @TaxedResult + @cashAmount;
    END

Output

(SELECT TAXEDRESULT + CASHAMOUNT AS RESULT 
              FROM(
                         SELECT CASHAMOUNT + 35 AS TAXEDRESULT
                            	FROM(
                                    SELECT CASHAMOUNT AS RESULT
                                ) T1
                        ) T2)

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.

IF statements can be well handled by JavaScript and Snowflake Scripting in Snowflake, but if you're coming from SQL Server, it's important to note the transformation that will best complete the transformation. For more information, you can give SnowConvert for SQL Server a try inside of BlackDiamond Studio or reach out to learn more about the kind of transformations done by SnowConvert.