Provides conditional branching.

Syntax

---

CASE
WHEN <BooleanExpression> THEN
   <StatementBlock>
[WHEN <BooleanExpression> THEN
   <StatementBlock>]
[ELSE
   <StatementBlock>]
END CASE;

Shorthand Value Syntax

CASE <Expression>
WHEN <Expression> THEN
   <StatementBlock>
[WHEN <Expression> THEN
   <StatementBlock>]
[ELSE
   <StatementBlock>]
END CASE;

<StatementBlock> =

[[Label:]
BEGIN]
   [<Statement>;]
   [<Statement>;]
[EXCEPTION]
   [<Statement>;]
[END [Label];]

Usage

---

Use this statement to provide conditional branching based upon a single or multiple boolean expressions, or based upon multiple simple value comparisons.

Examples

---

-- This procedure produces a summary
-- of the number of albums and total album
-- purchases by genre, label, or artist

CREATE PROCEDURE Summaries(IN "SummaryType" CHAR(1) COLLATE ANSI_CI)
BEGIN
    DECLARE Result CURSOR WITH RETURN FOR Stmt;

    CASE SummaryType
    -- Genres summary
    WHEN 'G' THEN
       BEGIN
       PREPARE Stmt FROM 'SELECT Genre AS Name, COUNT(Name) AS NumAlbums,
                          SUM(PurchasePrice) AS TotalPurchases
                          FROM Albums
                          GROUP BY Genre';
       OPEN Result;
       END;
    -- Labels summary
    WHEN 'L' THEN
       BEGIN
       PREPARE Stmt FROM 'SELECT Label AS Name, COUNT(Name) AS NumAlbums,
                          SUM(PurchasePrice) AS TotalPurchases
                          FROM Albums
                          GROUP BY Label';
       OPEN Result;
       END;
    -- Artists summary
    WHEN 'A' THEN
       BEGIN
       PREPARE Stmt FROM 'SELECT Artist AS Name, COUNT(Name) AS NumAlbums,
                          SUM(PurchasePrice) AS TotalPurchases
                          FROM Albums
                          GROUP BY Artist';
       OPEN Result;
       END;
    END CASE;
END

SQL 2003 Standard Deviations

---

This statement deviates from the SQL 2003 standard in the following ways: