CASE
Translation reference to convert SQL Server Case expression to Snowflake Scripting
Last updated
Translation reference to convert SQL Server Case expression to Snowflake Scripting
Last updated
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:
CASE
WHEN boolean_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
ENDNote: SQL Server allows to optionally encapsulate the input_expression and the boolean_expression in parentheses; Snowflake Scripting too.
The following examples detail two scenarios where the Case expression can be used and their differences from Snowflake Scripting.
CREATE OR ALTER PROCEDURE SelectCaseDemoProcedure
AS
SELECT TOP 10
LOGINID,
CASE (MARITALSTATUS)
WHEN 'S' THEN 'SINGLE'
WHEN 'M' THEN 'MARIED'
ELSE 'OTHER'
END AS status
FROM HUMANRESOURCES.EMPLOYEE;
GO
EXEC SelectCaseDemoProcedure;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.
CREATE OR REPLACE PROCEDURE SelectCaseDemoProcedure ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
ProcedureResultSet RESULTSET;
BEGIN
ProcedureResultSet := (
SELECT TOP 10
LOGINID,
CASE (MARITALSTATUS)
WHEN 'S' THEN 'SINGLE'
WHEN 'M' THEN 'MARIED'
ELSE 'OTHER'
END AS status
FROM
HUMANRESOURCES.EMPLOYEE);
RETURN TABLE(ProcedureResultSet);
END;
$$;
CALL SelectCaseDemoProcedure();The AdventureWorks2019 database was used in both languages to obtain the same results.
CREATE OR ALTER PROCEDURE SetCaseDemoProcedure
AS
DECLARE @value INT;
DECLARE @result INT;
SET @value = 5;
SET @result =
CASE @value
WHEN 1 THEN @value * 10
WHEN 3 THEN @value * 20
WHEN 5 THEN @value * 30
WHEN 7 THEN @value * 40
ELSE -1
END;
RETURN @result
GO
DECLARE @result INT;
EXEC @result = SetCaseDemoProcedure;
PRINT @result;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.
CREATE OR REPLACE PROCEDURE SetCaseDemoProcedure ()
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;
RESULT INT;
BEGIN
VALUE := 5;
CASE (:VALUE)
WHEN 1 THEN
RESULT := :VALUE * 10;
WHEN 3 THEN
RESULT := :VALUE * 20;
WHEN 5 THEN
RESULT := :VALUE * 30;
WHEN 7 THEN
RESULT := :VALUE * 40;
ELSE
RESULT := -1;
END;
RETURN :RESULT;
END;
$$;
DECLARE
RESULT INT;
BEGIN
CALL SetCaseDemoProcedure();
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'Print' NODE ***/!!!
PRINT @result;
END;No issues were found.
SSC-EWI-0073: Pending Functional Equivalence Review.