CASE (Snowflake Scripting)¶

A CASE statement provides a way to specify multiple conditions.

For more information on branching constructs, see Working with conditional logic.

Note

This Snowflake Scripting construct is valid only within a Snowflake Scripting block.

Syntax¶

Simple CASE statement:

CASE ( <expression_to_match> )
    WHEN <expression> THEN
        <statement>;
        [ <statement>; ... ]
    [ WHEN ... ]
    [ ELSE
        <statement>;
        [ <statement>; ... ]
    ]
END [ CASE ] ;

Where:

expression_to_match
The expression to match.

expression
If the value of this expression matches the value of expression_to_match, then the statements in this clause are executed.

statement
A statement can be any of the following:

A single SQL statement (including CALL).

A control-flow statement (for example, a looping or branching statement).

A nested block.

Searched CASE statement:

CASE
    WHEN <boolean_expression> THEN
        <statement>;
        [ <statement>; ... ]
    [ WHEN ... ]
    [ ELSE
        <statement>;
        [ <statement>; ... ]
    ]
END [ CASE ] ;

Where:

boolean_expression
If this expression evaluates to TRUE, then the statements in this clause are executed.

statement
A statement can be any of the following:

A single SQL statement (including CALL).

A control-flow statement (for example, a looping or branching statement).

A nested block.

Usage notes¶

If more than one branch of the CASE would match the expression, only the first is used.
When you compare expressions, NULL does not match NULL. If you wish to test explicitly for NULL values, use IS [ NOT ] NULL.

Examples¶

This example demonstrates a simple CASE statement:

CREATE PROCEDURE case_demo_01(v VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
  BEGIN
    CASE (v)
      WHEN 'first choice' THEN
        RETURN 'one';
      WHEN 'second choice' THEN
        RETURN 'two';
      ELSE
        RETURN 'unexpected choice';
    END;
  END;

Note: If you use Snowflake CLI, SnowSQL, the Classic Console, or the execute_stream or execute_string method in Python Connector code, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, and Python Connector):

CREATE PROCEDURE case_demo_01(v VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
    BEGIN
        CASE (v)
            WHEN 'first choice' THEN
                RETURN 'one';
            WHEN 'second choice' THEN
                RETURN 'two';
            ELSE
                RETURN 'unexpected choice';
       END CASE;
    END;
$$
;

When you call this stored procedure, the procedure produces the following output:

CALL case_demo_01('second choice');
+--------------+
| CASE_DEMO_01 |
|--------------|
| two          |
+--------------+

This example demonstrates a searched CASE statement:

CREATE PROCEDURE case_demo_2(v VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
  BEGIN
    CASE
      WHEN v = 'first choice' THEN
        RETURN 'one';
      WHEN v = 'second choice' THEN
        RETURN 'two';
      ELSE
        RETURN 'unexpected choice';
    END;
  END;

CREATE PROCEDURE case_demo_2(v VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
    BEGIN
        CASE 
            WHEN v = 'first choice' THEN
                RETURN 'one';
            WHEN v = 'second choice' THEN
                RETURN 'two';
            ELSE
                RETURN 'unexpected choice';
       END CASE;
    END;
$$
;

When you call this stored procedure, the procedure produces the following output:

CALL case_demo_2('none of the above');
+-------------------+
| CASE_DEMO_2       |
|-------------------|
| unexpected choice |
+-------------------+