# Working with Branching Constructs¶

Snowflake Scripting supports the following branching constructs:

• IF-THEN-ELSEIF-ELSE

• CASE

In this Topic:

## 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 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 are using SnowSQL or the classic web interface, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Web Interface):

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.

## 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 web interface, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Web Interface):

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.

### 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 web interface, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Web Interface):

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.