CASE

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:  
CASE  
     WHEN 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.

Select using Case

SQL Server

Simple CASE

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;

Searched CASE

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;

Result

sqlLOGINID              |status|
------------------------+------+
adventure-works\ken0    |SINGLE|
adventure-works\terri0  |SINGLE|
adventure-works\roberto0|MARIED|
adventure-works\rob0    |SINGLE|
adventure-works\gail0   |MARIED|
adventure-works\jossef0 |MARIED|
adventure-works\dylan0  |MARIED|
adventure-works\diane1  |SINGLE|
adventure-works\gigi0   |MARIED|
adventure-works\michael6|MARIED|

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.

Simple CASE

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();

Searched CASE

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'