IF

Translation reference to convert Transact-SQL IF..ELSE clauses to Snowflake Scripting

Applies to

• SQL Server

• Azure Synapse Analytics

Description

The IF clause allows an SQL statement or a block of statements to be conditionally executed as long as the Boolean expression is true; otherwise, the statements in the optional ELSE clause will be executed. Transact-SQL also supports embedding multiple IF... ELSE clauses in case multiple conditions are required, or the CASE clause can also be used.

For more information for Transact-SQL IF...ELSE, check here.

IF Boolean_expression   
     { sql_statement | statement_block }   
[ ELSE   
     { sql_statement | statement_block } ]   

Note: To define a statement block, use the control-of-flow keywords BEGIN and END.

Sample Source Patterns

Transact-SQL

The following code refers to an IF... ELSE in Transact-SQL that conditions the variable @value to identify if it is less than 5, if it is between 5 and 10, or if it has any other value. Since @value is initialized as 7, the second condition must be true and the result must be 200.

IF...ELSE

IN -> SqlServer_01.sql

CREATE OR ALTER PROCEDURE IfElseDemoProcedure
AS
    DECLARE @value INT;
    SET @value = 7;

    IF @value < 5
        SET @value = 100;
    ELSE IF @value >= 5 AND @value < 10
        BEGIN
            SET @value = 300;
            SET @value = @value - 100;
        END;
    ELSE  
        SET @value = -1;


    RETURN @value
GO


DECLARE @result INT;
EXEC @result = IfElseDemoProcedure;
PRINT @result;

Result

|result|
|------|
|200   |

Snowflake Scripting

Notice that in Snowflake Scripting, the embedded IF... ELSE condition is called ELSEIF.

Besides, the Boolean condition is encapsulated in parentheses and the clause always ends with the END IF expression.

In addition, in Snowflake Scripting it is not necessary to use the BEGIN and END keywords to define a statement block, however it can be used if required.

IF...ELSE

OUT -> SqlServer_01.sql

CREATE OR REPLACE PROCEDURE IfElseDemoProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        VALUE INT;
    BEGIN
         
        VALUE := 7;
        IF (:VALUE < 5) THEN
            VALUE := 100;
        ELSEIF (:VALUE >= 5 AND :VALUE < 10) THEN
            BEGIN
                VALUE := 300;
                VALUE := :VALUE - 100;
            END;
        ELSE
            VALUE := -1;
        END IF;
        RETURN :VALUE;
    END;
$$;

DECLARE
    RESULT INT;
BEGIN
    CALL IfElseDemoProcedure();
    !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'Print' NODE ***/!!!
    PRINT @result;
END;

Result

|result|
|------|
|200   |

IF statement outside routines (functions and procedures)

Unlike Transact-SQL, Snowflake does not support executing isolated statements like IF...ELSE outside routines like functions or procedures. For this scenario, the statement should be encapsulated in an anonymous block, as shown in the following example. You can read more about how to correctly return the output values in the SELECT section.

Transact-SQL

IN -> SqlServer_02.sql

DECLARE @maxWeight FLOAT, @productKey INTEGER  
SET @maxWeight = 100.00  
SET @productKey = 424  
IF @maxWeight <= 99  
    SELECT @productKey,  'This product is too heavy to ship and is only available for pickup.' 
ELSE  
    SELECT @productKey, 'This product is available for shipping or pickup.' 

Snowflake Scripting

OUT -> SqlServer_02.sql

DECLARE
    MAXWEIGHT FLOAT;
    PRODUCTKEY INTEGER;
    BlockResultSet1 VARCHAR;
    BlockResultSet2 VARCHAR;
    return_arr ARRAY := array_construct();
BEGIN
    MAXWEIGHT := 100.00;
    PRODUCTKEY := 424;
    IF (:MAXWEIGHT <= 99) THEN
        BlockResultSet1 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
        CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:BlockResultSet1) AS
            SELECT
                :PRODUCTKEY,  'This product is too heavy to ship and is only available for pickup.';
        return_arr := array_append(return_arr, :BlockResultSet1);
    ELSE
        BlockResultSet2 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
        CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:BlockResultSet2) AS
            SELECT
                :PRODUCTKEY, 'This product is available for shipping or pickup.';
        return_arr := array_append(return_arr, :BlockResultSet2);
    END IF;
    --** SSC-FDM-0020 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
    RETURN return_arr;
END;

Known Issues

No issues were found.

Related EWIs

1. SSC-EWI-0073: Pending Functional Equivalence Review.
2. SSC-FDM-0020: Multiple result sets are returned in temporary tables.