Translation reference to convert SQL Server Case expression to Snowflake Scripting
Some parts in the output code are omitted for clarity reasons.
Description
SQL Server has two possible formats of the Case expression. both of them for the purpose of evaluating expressions and conditionally obtaining results. The first one refers to a Simple Case Expression that will evaluate if an input_expression matches one or more of the when_expression. The second one will evaluate each Boolean_expression independently. The else clause is supported in both formats.
According to the official SQL Server Case documentation:
CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.
For more information regarding SQL Server Case, check here.
-- Simple CASE expression: CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END-- Searched CASE expression: CASEWHEN boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END
Note: SQL Server allows to optionally encapsulate the input_expression and the boolean_expression in parentheses; Snowflake Scripting too.
Sample Source Patterns
The following examples detail two scenarios where the Case expression can be used and their differences from Snowflake Scripting.
Note that in this scenario there are no differences regarding the Case expression itself.
The declaration and assignment of the res variable is in order to demonstrate the functional equivalence between both languages. It does not appear in the actual output.
Snowflake Scripting does not allow to set a case expression directly to a variable. Both SQL Server Case expression formats translate to the following grammar in Snowflake Scripting.
OUT -> SqlServer_03.sql
CREATEORREPLACEPROCEDURE SetCaseDemoProcedure ()RETURNSVARCHARLANGUAGESQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'EXECUTEASCALLERAS$$DECLAREVALUEINT; RESULT INT;BEGINVALUE :=5;CASE (:VALUE)WHEN1THEN RESULT := :VALUE*10;WHEN3THEN RESULT := :VALUE*20;WHEN5THEN RESULT := :VALUE*30;WHEN7THEN RESULT := :VALUE*40;ELSE RESULT :=-1;END;RETURN :RESULT;END;$$;DECLARE RESULT INT;BEGINCALL SetCaseDemoProcedure(); !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'Print' NODE ***/!!!PRINT @result;END;
OUT -> SqlServer_04.sql
CREATEORREPLACEPROCEDURE SetCaseDemoProcedure ()RETURNSVARCHARLANGUAGESQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'EXECUTEASCALLERAS$$DECLAREVALUEINT; RESULT INT;BEGINVALUE :=5;CASEWHEN :VALUE=1THEN RESULT := :VALUE*10;WHEN :VALUE=3THEN RESULT := :VALUE*20;WHEN :VALUE=5THEN RESULT := :VALUE*30;WHEN :VALUE=7THEN RESULT := :VALUE*40;ELSE RESULT :=-1;END;RETURN :RESULT;END;$$;DECLARE RESULT INT;BEGINCALL SetCaseDemoProcedure(); !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'Print' NODE ***/!!!PRINT @result;END;