Translation reference to convert Transact-SQL Case expression to Snowflake Scripting
Applies to
Some parts in the output code are omitted for clarity reasons.
Description
Transact-SQL 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 Transact-SQL 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 Transact-SQL 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 ]
END
Note: Transact-SQL 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.
Select using Case
Transact-SQL
IN -> SqlServer_01.sql
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;
IN -> SqlServer_02.sql
CREATE OR ALTER PROCEDURE SelectCaseDemoProcedure
AS
SELECT TOP 10
LOGINID,
CASE
WHEN MARITALSTATUS = 'S' THEN 'SINGLE'
WHEN MARITALSTATUS = '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.
OUT -> SqlServer_01.sql
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();
OUT -> SqlServer_02.sql
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
WHEN MARITALSTATUS = 'S' THEN 'SINGLE'
WHEN MARITALSTATUS = '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.
Transact-SQL
IN -> SqlServer_03.sql
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;
IN -> SqlServer_04.sql
CREATE OR ALTER PROCEDURE SetCaseDemoProcedure
AS
DECLARE @value INT;
DECLARE @result INT;
SET @value = 5;
SET @result =
CASE
WHEN @value = 1 THEN @value * 10
WHEN @value = 3 THEN @value * 20
WHEN @value = 5 THEN @value * 30
WHEN @value = 7 THEN @value * 40
ELSE -1
END;
RETURN @result
GO
DECLARE @result INT;
EXEC @result = SetCaseDemoProcedure;
PRINT @result;
|result|
|------|
|150 |
Snowflake Scripting
Snowflake Scripting does not allow to set a case expression directly to a variable. Both Transact-SQL Case expression formats translate to the following grammar in Snowflake Scripting.
OUT -> SqlServer_03.sql
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;
OUT -> SqlServer_04.sql
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
WHEN :VALUE = 1 THEN
RESULT := :VALUE * 10;
WHEN :VALUE = 3 THEN
RESULT := :VALUE * 20;
WHEN :VALUE = 5 THEN
RESULT := :VALUE * 30;
WHEN :VALUE = 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;
