What are the most significant differences between MySQL functions and procedures?

The most significant difference between procedures and functions is that they are invoked differently and for different purposes. Other than that following are the differences between procedure and functions −

• A procedure does not return a value. Instead, it is invoked with a CALL statement to perform an operation such as modifying a table or processing retrieved records.

  On the other hand, a function is invoked within an expression and returns a single value directly to the caller to be used in the expression. That is, a function is used in expressions the same way as a constant, a built-in function, or a reference to a table column.

• We cannot invoke a function with a CALL statement. We cannot invoke a procedure in an expression.

• The syntax for routine creation differs somewhat from procedures and functions as follows −

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:
   COMMENT 'string'
   | LANGUAGE SQL
   | [NOT] DETERMINISTIC
   | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
   | SQL SECURITY { DEFINER | INVOKER }

routine_body:
   Valid SQL routine statement

• Procedure parameters can be defined as input-only, output-only, or for both input and output. This means that a procedure can pass values back to the caller by using output parameters. These values can be accessed in statements that follow the CALL statement.

  On the other hand, functions have only input parameters. As a result, although both procedures and functions can have parameters, procedure parameter declaration syntax differs from that for functions.

• Functions return a value, so there must be a RETURNS clause in a function definition to indicate the data type of the return value. Also, there must be at least one RETURN statement within the function body to return a value to the caller.

  On the other hand, RETURNS and RETURN do not appear in procedure definitions.