<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="case">
      </a>
      15.6.5.1 CASE Statement
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045176357024">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa78574239"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token operator">CASE</span> <em class="replaceable">case_value</em>
    <span class="token keyword">WHEN</span> <em class="replaceable">when_value</em> <span class="token keyword">THEN</span> <em class="replaceable">statement_list</em>
    <span class="token punctuation">[</span><span class="token keyword">WHEN</span> <em class="replaceable">when_value</em> <span class="token keyword">THEN</span> <em class="replaceable">statement_list</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
    <span class="token punctuation">[</span><span class="token keyword">ELSE</span> <em class="replaceable">statement_list</em><span class="token punctuation">]</span>
<span class="token keyword">END</span> <span class="token operator">CASE</span></code></pre>
  </div>
  <p>
   Or:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa32545953"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token operator">CASE</span>
    <span class="token keyword">WHEN</span> <em class="replaceable">search_condition</em> <span class="token keyword">THEN</span> <em class="replaceable">statement_list</em>
    <span class="token punctuation">[</span><span class="token keyword">WHEN</span> <em class="replaceable">search_condition</em> <span class="token keyword">THEN</span> <em class="replaceable">statement_list</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
    <span class="token punctuation">[</span><span class="token keyword">ELSE</span> <em class="replaceable">statement_list</em><span class="token punctuation">]</span>
<span class="token keyword">END</span> <span class="token operator">CASE</span></code></pre>
  </div>
  <p>
   The
   <a class="link" href="case.html" title="15.6.5.1 CASE Statement">
    <code class="literal">
     CASE
    </code>
   </a>
   statement for stored
        programs implements a complex conditional construct.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    There is also a
    <a class="link" href="flow-control-functions.html#operator_case">
     <code class="literal">
      CASE
     </code>
    </a>
    <span class="emphasis">
     <em>
      operator
     </em>
    </span>
    , which differs from the
    <a class="link" href="case.html" title="15.6.5.1 CASE Statement">
     <code class="literal">
      CASE
     </code>
    </a>
    <span class="emphasis">
     <em>
      statement
     </em>
    </span>
    described here. See
    <a class="xref" href="flow-control-functions.html" title="14.5 Flow Control Functions">
     Section 14.5, “Flow Control Functions”
    </a>
    . The
    <a class="link" href="case.html" title="15.6.5.1 CASE Statement">
     <code class="literal">
      CASE
     </code>
    </a>
    statement cannot have an
    <code class="literal">
     ELSE NULL
    </code>
    clause, and it is terminated with
    <code class="literal">
     END CASE
    </code>
    instead of
    <code class="literal">
     END
    </code>
    .
   </p>
  </div>
  <p>
   For the first syntax,
   <em class="replaceable">
    <code>
     case_value
    </code>
   </em>
   is
        an expression. This value is compared to the
   <em class="replaceable">
    <code>
     when_value
    </code>
   </em>
   expression in each
   <code class="literal">
    WHEN
   </code>
   clause until one of them is equal. When
        an equal
   <em class="replaceable">
    <code>
     when_value
    </code>
   </em>
   is found, the
        corresponding
   <code class="literal">
    THEN
   </code>
   clause
   <em class="replaceable">
    <code>
     statement_list
    </code>
   </em>
   executes. If no
   <em class="replaceable">
    <code>
     when_value
    </code>
   </em>
   is equal, the
   <code class="literal">
    ELSE
   </code>
   clause
   <em class="replaceable">
    <code>
     statement_list
    </code>
   </em>
   executes, if there is
        one.
  </p>
  <p>
   This syntax cannot be used to test for equality with
   <code class="literal">
    NULL
   </code>
   because
   <code class="literal">
    NULL = NULL
   </code>
   is false. See
   <a class="xref" href="working-with-null.html" title="5.3.4.6 Working with NULL Values">
    Section 5.3.4.6, “Working with NULL Values”
   </a>
   .
  </p>
  <p>
   For the second syntax, each
   <code class="literal">
    WHEN
   </code>
   clause
   <em class="replaceable">
    <code>
     search_condition
    </code>
   </em>
   expression is
        evaluated until one is true, at which point its corresponding
   <code class="literal">
    THEN
   </code>
   clause
   <em class="replaceable">
    <code>
     statement_list
    </code>
   </em>
   executes. If no
   <em class="replaceable">
    <code>
     search_condition
    </code>
   </em>
   is equal, the
   <code class="literal">
    ELSE
   </code>
   clause
   <em class="replaceable">
    <code>
     statement_list
    </code>
   </em>
   executes, if there is
        one.
  </p>
  <p>
   If no
   <em class="replaceable">
    <code>
     when_value
    </code>
   </em>
   or
   <em class="replaceable">
    <code>
     search_condition
    </code>
   </em>
   matches the value
        tested and the
   <a class="link" href="case.html" title="15.6.5.1 CASE Statement">
    <code class="literal">
     CASE
    </code>
   </a>
   statement
        contains no
   <code class="literal">
    ELSE
   </code>
   clause, a
   <span class="errortext">
    Case
        not found for CASE statement
   </span>
   error results.
  </p>
  <p>
   Each
   <em class="replaceable">
    <code>
     statement_list
    </code>
   </em>
   consists of one
        or more SQL statements; an empty
   <em class="replaceable">
    <code>
     statement_list
    </code>
   </em>
   is not permitted.
  </p>
  <p>
   To handle situations where no value is matched by any
   <code class="literal">
    WHEN
   </code>
   clause, use an
   <code class="literal">
    ELSE
   </code>
   containing an empty
   <a class="link" href="begin-end.html" title="15.6.1 BEGIN ... END Compound Statement">
    <code class="literal">
     BEGIN ...
        END
    </code>
   </a>
   block, as shown in this example. (The indentation
        used here in the
   <code class="literal">
    ELSE
   </code>
   clause is for purposes
        of clarity only, and is not otherwise significant.)
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa67921940"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">DELIMITER</span> <span class="token operator">|</span>

<span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> p<span class="token punctuation">(</span><span class="token punctuation">)</span>
  <span class="token keyword">BEGIN</span>
    <span class="token keyword">DECLARE</span> v <span class="token datatype">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">1</span><span class="token punctuation">;</span>

    <span class="token operator">CASE</span> v
      <span class="token keyword">WHEN</span> <span class="token number">2</span> <span class="token keyword">THEN</span> <span class="token keyword">SELECT</span> v<span class="token punctuation">;</span>
      <span class="token keyword">WHEN</span> <span class="token number">3</span> <span class="token keyword">THEN</span> <span class="token keyword">SELECT</span> <span class="token number">0</span><span class="token punctuation">;</span>
      <span class="token keyword">ELSE</span>
        <span class="token keyword">BEGIN</span>
        <span class="token keyword">END</span><span class="token punctuation">;</span>
    <span class="token keyword">END</span> <span class="token operator">CASE</span><span class="token punctuation">;</span>
  <span class="token keyword">END</span><span class="token punctuation">;</span>
  <span class="token operator">|</span></code></pre>
  </div>
 </div>
 <br/>
</div>
