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.
Searched CASE statement:
CASE WHEN <boolean_expression> THEN <statement>; [ <statement>; ... ] [ WHEN ... ] [ ELSE <statement>; [ <statement>; ... ] ] END [ CASE ] ;
Where:
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 are using SnowSQL, the Classic Console, or the
execute_stream
orexecute_string
method in Python Connector code, use this example instead (see Using Snowflake Scripting in SnowSQL, the Classic Console, 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;Note: If you are using SnowSQL, the Classic Console, or the
execute_stream
orexecute_string
method in Python Connector code, use this example instead (see Using Snowflake Scripting in SnowSQL, the Classic Console, and Python Connector):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 | +-------------------+