Working with Branching Constructs¶
Snowflake Scripting supports the following branching constructs:
IF-THEN-ELSEIF-ELSE
CASE
IF Statements¶
In Snowflake Scripting, you can execute a set of statements if a condition is met by using an IF statement.
The syntax for the IF
statement is:
IF (<condition>) THEN -- Statements to execute if the <condition> is true. [ ELSEIF ( <condition_2> ) THEN -- Statements to execute if the <condition_2> is true. ] [ ELSE -- Statements to execute if none of the conditions is 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 if none of the conditions evaluate to true, add an
ELSE
clause.The
ELSEIF
andELSE
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 are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):
EXECUTE IMMEDIATE $$ BEGIN LET count := 1; IF (count < 0) THEN return 'negative value'; ELSEIF (count = 0) THEN return 'zero'; ELSE return 'positive value'; END IF; END; $$ ;
For the full syntax and details on 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 ( <expression_to_match> ) 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 value_n_of_expression
matches the value of expression_to_match
.
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 are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):
EXECUTE IMMEDIATE $$ 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; $$ ;
For the full syntax and details on 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 <condition_1> THEN <statement>; [ <statement>; ... ] [ WHEN <condition_2> THEN <statement>; [ <statement>; ... ] ] ... -- Additional WHEN clauses for other possible conditions; [ ELSE <statement>; [ <statement>; ... ] ] 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 are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):
EXECUTE IMMEDIATE $$ 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; $$ ;
For the full syntax and details on CASE
statements, see CASE (Snowflake Scripting).