Syntax:
CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body
    
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
    
func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

routine_body:
    Valid SQL procedure statement

These statements create stored routines. As of MySQL 5.0.3, to use
them, it is necessary to have the CREATE ROUTINE privilege. If binary
logging is enabled, these statements might also require the SUPER
privilege, as described in
http://dev.mysql.com/doc/refman/5.0/en/stored-procedure-logging.html.
MySQL automatically grants the ALTER ROUTINE and EXECUTE privileges to
the routine creator.

By default, the routine is associated with the default database. To
associate the routine explicitly with a given database, specify the
name as db_name.sp_name when you create it.

If the routine name is the same as the name of a built-in SQL function,
you must use a space between the name and the following parenthesis
when defining the routine, or a syntax error occurs. This is also true
when you invoke the routine later. For this reason, we suggest that it
is better to avoid re-using the names of existing SQL functions for
your own stored routines.

The IGNORE_SPACE SQL mode applies to built-in functions, not to stored
routines. It is always allowable to have spaces after a routine name,
regardless of whether IGNORE_SPACE is enabled.

The parameter list enclosed within parentheses must always be present.
If there are no parameters, an empty parameter list of () should be
used.

Each parameter can be declared to use any valid data type, except that
the COLLATE attribute cannot be used.

Each parameter is an IN parameter by default. To specify otherwise for
a parameter, use the keyword OUT or INOUT before the parameter name.

Note: Specifying a parameter as IN, OUT, or INOUT is valid only for a
PROCEDURE. (FUNCTION parameters are always regarded as IN parameters.)

An IN parameter passes a value into a procedure. The procedure might
modify the value, but the modification is not visible to the caller
when the procedure returns. An OUT parameter passes a value from the
procedure back to the caller. Its initial value is NULL within the
procedure, and its value is visible to the caller when the procedure
returns. An INOUT parameter is initialized by the caller, can be
modified by the procedure, and any change made by the procedure is
visible to the caller when the procedure returns.

For each OUT or INOUT parameter, pass a user-defined variable so that
you can obtain its value when the procedure returns. (For an example,
see [HELP CALL].) If you are calling the procedure from within another
stored procedure or function, you can also pass a routine parameter or
local routine variable as an IN or INOUT parameter.

The RETURNS clause may be specified only for a FUNCTION, for which it
is mandatory. It indicates the return type of the function, and the
function body must contain a RETURN value statement. If the RETURN
statement returns a value of a different type, the value is coerced to
the proper type. For example, if a function specifies an ENUM or SET
value in the RETURNS clause, but the RETURN statement returns an
integer, the value returned from the function is the string for the
corresponding ENUM member of set of SET members.

The routine_body consists of a valid SQL procedure statement. This can
be a simple statement such as SELECT or INSERT, or it can be a compound
statement written using BEGIN and END. Compound statement syntax is
described in [HELP BEGIN END]. Compound statements can contain
declarations, loops, and other control structure statements. The syntax
for these statements is described later in this chapter. See, for
example, http://dev.mysql.com/doc/refman/5.0/en/declare.html, and [HELP
IF statement]. Some statements are not allowed in stored routines; see
http://dev.mysql.com/doc/refman/5.0/en/routine-restrictions.html.

MySQL stores the sql_mode system variable setting that is in effect at
the time a routine is created, and always executes the routine with
this setting in force, regardless of the current server SQL mode.

URL: http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

