Working with conditional logic | Snowflake Documentation (original) (raw)
Snowflake Scripting supports the following branching constructs for conditional logic:
- IF-THEN-ELSEIF-ELSE
- CASE
IF statements¶
In Snowflake Scripting, you can execute a set of statements if a condition is met by using anIF statement.
The syntax for the IF statement is:
IF () THEN -- Statements to execute if the is true.
[ ELSEIF ( ) THEN -- Statements to execute if the is true. ]
[ ELSE -- Statements to execute if none of the conditions are true. ]
END IF ;
In an IF statement:
- If you need to specify additional conditions, add an ELSEIF clause for each condition.
- To specify the statements to execute when none of the conditions evaluate to TRUE, add an ELSE clause.
- The ELSEIF and ELSE clauses are optional.
The following is a simple example of an IF statement:
BEGIN LET count := 1; IF (count < 0) THEN RETURN 'negative value'; ELSEIF (count = 0) THEN RETURN 'zero'; ELSE RETURN 'positive value'; END IF; END;
Note: If you use Snowflake CLI, SnowSQL, the Classic Console, or theexecute_stream or execute_string method in Python Connectorcode, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, the Classic Console, and Python Connector):
EXECUTE IMMEDIATE BEGINLETcount:=1;IF(count<0)THENRETURN′negativevalue′;ELSEIF(count=0)THENRETURN′zero′;ELSERETURN′positivevalue′;ENDIF;END;BEGIN LET count := 1; IF (count < 0) THEN RETURN 'negative value'; ELSEIF (count = 0) THEN RETURN 'zero'; ELSE RETURN 'positive value'; END IF; END;BEGINLETcount:=1;IF(count<0)THENRETURN′negativevalue′;ELSEIF(count=0)THENRETURN′zero′;ELSERETURN′positivevalue′;ENDIF;END; ;
+-----------------+
| anonymous block |
|---|
| positive value |
| +-----------------+ |
For the full syntax and details about IF statements, see IF (Snowflake Scripting).
CASE statements¶
A CASE statement behaves similarly to an IF statement but provides a simpler way to specify multiple conditions.
Snowflake Scripting supports two forms of the CASE statement:
The next sections explain how to use these different forms.
Note
Snowflake supports other uses of the keyword CASE outside of Snowflake Scripting (e.g. the conditional expression CASE).
Simple CASE statements¶
In a simple CASE statement, you define different branches (WHEN clauses) for different possible values of a given expression.
The syntax for the simple CASE statement is:
CASE ( )
WHEN <value_1_of_expression> THEN
<statement>;
[ <statement>; ... ]
[ WHEN <value_2_of_expression> THEN
<statement>;
[ <statement>; ... ]
]
... -- Additional WHEN clauses for other possible values;
[ ELSE
<statement>;
[ <statement>; ... ]
]END [ CASE ] ;
Snowflake executes the first branch for which _valuenofexpression_ matches the value of _expressiontomatch_.
For example, suppose that you want to execute different statements, based on the value of the expression_to_evaluate variable. For each possible value of this variable (e.g. value a, value b, etc.), you can define a WHEN clause that specifies the statement(s) to execute:
DECLARE expression_to_evaluate VARCHAR DEFAULT 'default value'; BEGIN expression_to_evaluate := 'value a'; CASE (expression_to_evaluate) WHEN 'value a' THEN RETURN 'x'; WHEN 'value b' THEN RETURN 'y'; WHEN 'value c' THEN RETURN 'z'; WHEN 'default value' THEN RETURN 'default'; ELSE RETURN 'other'; END; END;
Note: If you use Snowflake CLI, SnowSQL, the Classic Console, or theexecute_stream or execute_string method in Python Connectorcode, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, the Classic Console, and Python Connector):
EXECUTE IMMEDIATE DECLAREexpressiontoevaluateVARCHARDEFAULT′defaultvalue′;BEGINexpressiontoevaluate:=′valuea′;CASE(expressiontoevaluate)WHEN′valuea′THENRETURN′x′;WHEN′valueb′THENRETURN′y′;WHEN′valuec′THENRETURN′z′;WHEN′defaultvalue′THENRETURN′default′;ELSERETURN′other′;END;END;DECLARE expression_to_evaluate VARCHAR DEFAULT 'default value'; BEGIN expression_to_evaluate := 'value a'; CASE (expression_to_evaluate) WHEN 'value a' THEN RETURN 'x'; WHEN 'value b' THEN RETURN 'y'; WHEN 'value c' THEN RETURN 'z'; WHEN 'default value' THEN RETURN 'default'; ELSE RETURN 'other'; END; END;DECLAREexpressiontoevaluateVARCHARDEFAULT′defaultvalue′;BEGINexpressiontoevaluate:=′valuea′;CASE(expressiontoevaluate)WHEN′valuea′THENRETURN′x′;WHEN′valueb′THENRETURN′y′;WHEN′valuec′THENRETURN′z′;WHEN′defaultvalue′THENRETURN′default′;ELSERETURN′other′;END;END; ;
+-----------------+
| anonymous block |
|---|
| x |
| +-----------------+ |
For the full syntax and details about CASE statements, see CASE (Snowflake Scripting).
Searched CASE statements¶
In the searched CASE statement, you specify different conditions for each branch (WHEN clause). Snowflake executes the first branch for which the expression evaluates to TRUE.
The syntax for the searched CASE statement is:
CASE
WHEN THEN ; [ ; ... ]
[ WHEN THEN ; [ ; ... ] ]
... -- Additional WHEN clauses for other possible conditions;
[ ELSE ; [ ; ... ] ]
END [ CASE ] ;
For example, when you execute the following CASE statement, the returned value is a is x because that branch is the first branch in which the expression evaluates to TRUE:
DECLARE a VARCHAR DEFAULT 'x'; b VARCHAR DEFAULT 'y'; c VARCHAR DEFAULT 'z'; BEGIN CASE WHEN a = 'x' THEN RETURN 'a is x'; WHEN b = 'y' THEN RETURN 'b is y'; WHEN c = 'z' THEN RETURN 'c is z'; ELSE RETURN 'a is not x, b is not y, and c is not z'; END; END;
Note: If you use Snowflake CLI, SnowSQL, the Classic Console, or theexecute_stream or execute_string method in Python Connectorcode, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, the Classic Console, and Python Connector):
EXECUTE IMMEDIATE DECLAREaVARCHARDEFAULT′x′;bVARCHARDEFAULT′y′;cVARCHARDEFAULT′z′;BEGINCASEWHENa=′x′THENRETURN′aisx′;WHENb=′y′THENRETURN′bisy′;WHENc=′z′THENRETURN′cisz′;ELSERETURN′aisnotx,bisnoty,andcisnotz′;END;END;DECLARE a VARCHAR DEFAULT 'x'; b VARCHAR DEFAULT 'y'; c VARCHAR DEFAULT 'z'; BEGIN CASE WHEN a = 'x' THEN RETURN 'a is x'; WHEN b = 'y' THEN RETURN 'b is y'; WHEN c = 'z' THEN RETURN 'c is z'; ELSE RETURN 'a is not x, b is not y, and c is not z'; END; END;DECLAREaVARCHARDEFAULT′x′;bVARCHARDEFAULT′y′;cVARCHARDEFAULT′z′;BEGINCASEWHENa=′x′THENRETURN′aisx′;WHENb=′y′THENRETURN′bisy′;WHENc=′z′THENRETURN′cisz′;ELSERETURN′aisnotx,bisnoty,andcisnotz′;END;END; ;
+-----------------+
| anonymous block |
|---|
| a is x |
| +-----------------+ |
For the full syntax and details about CASE statements, see CASE (Snowflake Scripting).