<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="local-variable-scope">
      </a>
      15.6.4.2 Local Variable Scope and Resolution
     </h4>
    </div>
   </div>
  </div>
  <p>
   The scope of a local variable is the
   <a class="link" href="begin-end.html" title="15.6.1 BEGIN ... END Compound Statement">
    <code class="literal">
     BEGIN ...
        END
    </code>
   </a>
   block within which it is declared. The variable
        can be referred to in blocks nested within the declaring block,
        except those blocks that declare a variable with the same name.
  </p>
  <p>
   Because local variables are in scope only during stored program
        execution, references to them are not permitted in prepared
        statements created within a stored program. Prepared statement
        scope is the current session, not the stored program, so the
        statement could be executed after the program ends, at which
        point the variables would no longer be in scope. For example,
   <code class="literal">
    SELECT ... INTO
    <em class="replaceable">
     <code>
      local_var
     </code>
    </em>
   </code>
   cannot be used as
        a prepared statement. This restriction also applies to stored
        procedure and function parameters. See
   <a class="xref" href="prepare.html" title="15.5.1 PREPARE Statement">
    Section 15.5.1, “PREPARE Statement”
   </a>
   .
  </p>
  <p>
   A local variable should not have the same name as a table
        column. If an SQL statement, such as a
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT ...
        INTO
    </code>
   </a>
   statement, contains a reference to a column and a
        declared local variable with the same name, MySQL currently
        interprets the reference as the name of a variable. Consider the
        following procedure definition:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa90742809"><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">CREATE</span> <span class="token keyword">PROCEDURE</span> sp1 <span class="token punctuation">(</span>x <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
  <span class="token keyword">DECLARE</span> xname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token string">'bob'</span><span class="token punctuation">;</span>
  <span class="token keyword">DECLARE</span> newname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
  <span class="token keyword">DECLARE</span> <span class="token keyword">xid</span> <span class="token datatype">INT</span><span class="token punctuation">;</span>

  <span class="token keyword">SELECT</span> xname<span class="token punctuation">,</span> id <span class="token keyword">INTO</span> newname<span class="token punctuation">,</span> <span class="token keyword">xid</span>
    <span class="token keyword">FROM</span> table1 <span class="token keyword">WHERE</span> xname <span class="token operator">=</span> xname<span class="token punctuation">;</span>
  <span class="token keyword">SELECT</span> newname<span class="token punctuation">;</span>
<span class="token keyword">END</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   MySQL interprets
   <code class="literal">
    xname
   </code>
   in the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement as a reference
        to the
   <code class="literal">
    xname
   </code>
   <span class="emphasis">
    <em>
     variable
    </em>
   </span>
   rather than the
   <code class="literal">
    xname
   </code>
   <span class="emphasis">
    <em>
     column
    </em>
   </span>
   . Consequently, when the procedure
   <code class="literal">
    sp1()
   </code>
   is called, the
   <code class="literal">
    newname
   </code>
   variable returns the value
   <code class="literal">
    'bob'
   </code>
   regardless of the value of the
   <code class="literal">
    table1.xname
   </code>
   column.
  </p>
  <p>
   Similarly, the cursor definition in the following procedure
        contains a
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement that
        refers to
   <code class="literal">
    xname
   </code>
   . MySQL interprets this as a
        reference to the variable of that name rather than a column
        reference.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa99421060"><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">CREATE</span> <span class="token keyword">PROCEDURE</span> sp2 <span class="token punctuation">(</span>x <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
  <span class="token keyword">DECLARE</span> xname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token string">'bob'</span><span class="token punctuation">;</span>
  <span class="token keyword">DECLARE</span> newname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
  <span class="token keyword">DECLARE</span> <span class="token keyword">xid</span> <span class="token datatype">INT</span><span class="token punctuation">;</span>
  <span class="token keyword">DECLARE</span> done <span class="token datatype">TINYINT</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">;</span>
  <span class="token keyword">DECLARE</span> cur1 <span class="token keyword">CURSOR</span> <span class="token keyword">FOR</span> <span class="token keyword">SELECT</span> xname<span class="token punctuation">,</span> id <span class="token keyword">FROM</span> table1<span class="token punctuation">;</span>
  <span class="token keyword">DECLARE</span> <span class="token keyword">CONTINUE</span> <span class="token keyword">HANDLER</span> <span class="token keyword">FOR</span> <span class="token operator">NOT</span> <span class="token keyword">FOUND</span> <span class="token keyword">SET</span> done <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>

  <span class="token keyword">OPEN</span> cur1<span class="token punctuation">;</span>
  read_loop: <span class="token keyword">LOOP</span>
    <span class="token keyword">FETCH</span> <span class="token keyword">FROM</span> cur1 <span class="token keyword">INTO</span> newname<span class="token punctuation">,</span> <span class="token keyword">xid</span><span class="token punctuation">;</span>
    <span class="token keyword">IF</span> done <span class="token keyword">THEN</span> <span class="token keyword">LEAVE</span> read_loop<span class="token punctuation">;</span> <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>
    <span class="token keyword">SELECT</span> newname<span class="token punctuation">;</span>
  <span class="token keyword">END</span> <span class="token keyword">LOOP</span><span class="token punctuation">;</span>
  <span class="token keyword">CLOSE</span> cur1<span class="token punctuation">;</span>
<span class="token keyword">END</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   See also
   <a class="xref" href="stored-program-restrictions.html" title="27.8 Restrictions on Stored Programs">
    Section 27.8, “Restrictions on Stored Programs”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
