# CASE Statement

## Syntax

```sql
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE
```

Or:

```sql
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list] 
END CASE
```

## Description

The text on this page describes the `CASE` statement for [stored programs](https://mariadb.com/docs/server/server-usage/stored-routines). See the [CASE OPERATOR](https://mariadb.com/docs/server/reference/sql-functions/control-flow-functions/case-operator) for details on the CASE operator outside of [stored programs](https://mariadb.com/docs/server/server-usage/stored-routines).

The `CASE` statement for [stored programs](https://mariadb.com/docs/server/server-usage/stored-routines) implements a complex conditional construct. If a `search_condition` evaluates to true, the corresponding SQL statement list is executed. If no search condition matches, the statement list in the `ELSE` clause is executed. Each `statement_list` consists of one or more statements.

The `CASE` statement cannot have an `ELSE NULL` clause, and it is terminated with `END CASE` instead of `END`. implements a complex conditional construct. If a `search_condition` evaluates to true, the corresponding SQL statement list is executed. If no search condition matches, the statement list in the `ELSE` clause is executed. Each `statement_list` consists of one or more statements.

If no when\_value or search\_condition matches the value tested and the `CASE` statement contains no `ELSE` clause, a Case not found for `CASE` statement error results.

Each statement\_list consists of one or more statements; an empty `statement_list` is not allowed. To handle situations where no value is matched by any `WHEN` clause, use an `ELSE` containing an empty [BEGIN ... END](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/begin-end) block, as shown in this example:

```sql
DELIMITER |
CREATE PROCEDURE p()
BEGIN
  DECLARE v INT DEFAULT 1;
  CASE v
    WHEN 2 THEN SELECT v;
    WHEN 3 THEN SELECT 0;
    ELSE BEGIN END;
  END CASE;
END;
|
```

The indentation used here in the `ELSE` clause is for purposes of clarity only and is not otherwise significant. See [Delimiters in the mariadb client](https://mariadb.com/docs/server/clients-and-utilities/mariadb-client/mariadb-command-line-client#delimiters) for more on the use of the delimiter command.

**Note:** The syntax of the `CASE` statement used inside stored programs differs slightly from that of the SQL CASE expression described in [CASE OPERATOR](https://mariadb.com/docs/server/reference/sql-functions/control-flow-functions/case-operator). The `CASE` statement cannot have an `ELSE NULL` clause, and it is terminated with `END CASE` instead of `END`.

## See Also

* The [CASE operator](https://mariadb.com/docs/server/reference/sql-functions/control-flow-functions/case-operator), which differs from the CASE statement described above.
* The [IF statement](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/if).

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/case-statement.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.