CASE (Snowflake Scripting)¶

Syntax¶

Simple CASE statement:

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

Copy

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 ] ; 

Copy

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; 

Copy

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, 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; $$ ; 

Copy

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

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

Copy

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; 

Copy

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, 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; $$ ; 

Copy

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 | +-------------------+ 

Copy