Translation reference to convert Transact-SQL IF..ELSE clauses to Snowflake Scripting
Applies to
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.
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.
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|
|------|
|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.
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|
|------|
|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 theSELECT 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;
