CASE (Snowflake Scripting) | Snowflake Documentation (original) (raw)

A CASE statement provides a way to specify multiple conditions.

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

Syntax¶

Simple CASE statement:

CASE ( ) WHEN THEN ; [ ; ... ] [ WHEN ... ] [ ELSE ; [ ; ... ] ] END [ CASE ] ;

Where:

_expressiontomatch_

The expression to match.

_expression_

If the value of this expression matches the value of _expressiontomatch_, 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 orbranching statement).
• A nested block.

Searched CASE statement:

CASE WHEN THEN ; [ ; ... ] [ WHEN ... ] [ ELSE ; [ ; ... ] ] END [ CASE ] ;

Where:

_booleanexpression_

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 orbranching 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, useIS [ NOT ] NULL.

Examples¶

This example demonstrates a simple CASE statement:

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:

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