<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="stored-routines-syntax">
      </a>
      27.2.1 Stored Routine Syntax
     </h3>
    </div>
   </div>
  </div>
  <p>
   A stored routine is either a procedure or a function. Stored
      routines are created with the
   <a class="link" href="create-procedure.html" title="15.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements">
    <code class="literal">
     CREATE
      PROCEDURE
    </code>
   </a>
   and
   <a class="link" href="create-function.html" title="15.1.14 CREATE FUNCTION Statement">
    <code class="literal">
     CREATE
      FUNCTION
    </code>
   </a>
   statements (see
   <a class="xref" href="create-procedure.html" title="15.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements">
    Section 15.1.17, “CREATE PROCEDURE and CREATE FUNCTION Statements”
   </a>
   ). A procedure is invoked using
      a
   <a class="link" href="call.html" title="15.2.1 CALL Statement">
    <code class="literal">
     CALL
    </code>
   </a>
   statement (see
   <a class="xref" href="call.html" title="15.2.1 CALL Statement">
    Section 15.2.1, “CALL Statement”
   </a>
   ), and can only pass back values using
      output variables. A function can be called from inside a statement
      just like any other function (that is, by invoking the function's
      name), and can return a scalar value. The body of a stored routine
      can use compound statements (see
   <a class="xref" href="sql-compound-statements.html" title="15.6 Compound Statement Syntax">
    Section 15.6, “Compound Statement Syntax”
   </a>
   ).
  </p>
  <p>
   Stored routines can be dropped with the
   <a class="link" href="drop-procedure.html" title="15.1.29 DROP PROCEDURE and DROP FUNCTION Statements">
    <code class="literal">
     DROP
      PROCEDURE
    </code>
   </a>
   and
   <a class="link" href="drop-function.html" title="15.1.26 DROP FUNCTION Statement">
    <code class="literal">
     DROP
      FUNCTION
    </code>
   </a>
   statements (see
   <a class="xref" href="drop-procedure.html" title="15.1.29 DROP PROCEDURE and DROP FUNCTION Statements">
    Section 15.1.29, “DROP PROCEDURE and DROP FUNCTION Statements”
   </a>
   ), and altered with the
   <a class="link" href="alter-procedure.html" title="15.1.7 ALTER PROCEDURE Statement">
    <code class="literal">
     ALTER PROCEDURE
    </code>
   </a>
   and
   <a class="link" href="alter-function.html" title="15.1.4 ALTER FUNCTION Statement">
    <code class="literal">
     ALTER FUNCTION
    </code>
   </a>
   statements (see
   <a class="xref" href="alter-procedure.html" title="15.1.7 ALTER PROCEDURE Statement">
    Section 15.1.7, “ALTER PROCEDURE Statement”
   </a>
   ).
  </p>
  <p>
   A stored procedure or function is associated with a particular
      database. This has several implications:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      When the routine is invoked, an implicit
      <code class="literal">
       USE
       <em class="replaceable">
        <code>
         db_name
        </code>
       </em>
      </code>
      is performed (and
          undone when the routine terminates).
      <a class="link" href="use.html" title="15.8.4 USE Statement">
       <code class="literal">
        USE
       </code>
      </a>
      statements within stored
          routines are not permitted.
     </p>
    </li>
    <li class="listitem">
     <p>
      You can qualify routine names with the database name. This can
          be used to refer to a routine that is not in the current
          database. For example, to invoke a stored procedure
      <code class="literal">
       p
      </code>
      or function
      <code class="literal">
       f
      </code>
      that is
          associated with the
      <code class="literal">
       test
      </code>
      database, you can
          say
      <code class="literal">
       CALL test.p()
      </code>
      or
      <code class="literal">
       test.f()
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      When a database is dropped, all stored routines associated
          with it are dropped as well.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Stored functions cannot be recursive.
  </p>
  <p>
   Recursion in stored procedures is permitted but disabled by
      default. To enable recursion, set the
   <a class="link" href="server-system-variables.html#sysvar_max_sp_recursion_depth">
    <code class="literal">
     max_sp_recursion_depth
    </code>
   </a>
   server
      system variable to a value greater than zero. Stored procedure
      recursion increases the demand on thread stack space. If you
      increase the value of
   <a class="link" href="server-system-variables.html#sysvar_max_sp_recursion_depth">
    <code class="literal">
     max_sp_recursion_depth
    </code>
   </a>
   , it may be
      necessary to increase thread stack size by increasing the value of
   <a class="link" href="server-system-variables.html#sysvar_thread_stack">
    <code class="literal">
     thread_stack
    </code>
   </a>
   at server startup.
      See
   <a class="xref" href="server-system-variables.html" title="7.1.8 Server System Variables">
    Section 7.1.8, “Server System Variables”
   </a>
   , for more
      information.
  </p>
  <p>
   MySQL supports a very useful extension that enables the use of
      regular
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statements (that is,
      without using cursors or local variables) inside a stored
      procedure. The result set of such a query is simply sent directly
      to the client. Multiple
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statements generate multiple result sets, so the client must use a
      MySQL client library that supports multiple result sets. This
      means the client must use a client library from a version of MySQL
      at least as recent as 4.1. The client should also specify the
   <code class="literal">
    CLIENT_MULTI_RESULTS
   </code>
   option when it connects.
      For C programs, this can be done with the
   <a class="ulink" href="/doc/c-api/8.4/en/mysql-real-connect.html" target="_top">
    <code class="literal">
     mysql_real_connect()
    </code>
   </a>
   C API
      function. See
   <a class="ulink" href="/doc/c-api/8.4/en/mysql-real-connect.html" target="_top">
    mysql_real_connect()
   </a>
   , and
   <a class="ulink" href="/doc/c-api/8.4/en/c-api-multiple-queries.html" target="_top">
    Multiple Statement Execution Support
   </a>
   .
  </p>
  <p>
   A user variable referenced by a statement in a stored procedure
      has its type determined the first time the procedure is invoked,
      and retains this type each time the procedure is invoked
      thereafter.
  </p>
 </div>
 <br/>
</div>
