IF

Translation reference to convert SQL Server IF..ELSE clauses to Snowflake Scripting

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. SQL Server 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 SQL Server 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

SQL Server

The following code refers to an IF... ELSE in SQL Server 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

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

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

CREATE OR REPLACE PROCEDURE PUBLIC.IfElseDemoProcedure()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      LET value INTEGER;
      value := 7;
      
      IF (value < 5) THEN
          value := 100;
      ELSEIF (value >= 5 AND value < 10) THEN
          value := 300;
          value := value - 100;
      ELSE
          value := 300;
      END IF;
      
      RETURN value;
   END;
$$;


CALL IfElseDemoProcedure();

Result

IF statement outside routines (functions and procedures)

Unlike SQL Server, 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.

SQL Server

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

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;
    --** MSC-WARNING - MSCEWI1100 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
    RETURN return_arr;
END;

Known Issues

No issues were found.

Related EWIs

No related EWIs.