What's the differences between stored procedures, functions and routines?

Google is your friend. The first match for "mysql routine function procedure" is this: http://dev.mysql.com/doc/refman/5.0/en/stored-routines-syntax.html

A quick summary:

A stored routine is either a procedure or a function.

A procedure is invoked using a CALL statement and can only pass back values using output variables.

A function can be called from inside a statement just like any other function and can return a scalar value.

Here I have tried to summarize the differences between functions and procedures:

1. A FUNCTION always returns a value using the return statement. PROCEDURE may return one or more values through parameters or may not return any at all.
2. Functions are normally used for computations where as procedures are normally used for executing business logic.
3. A Function returns 1 value only. Procedure can return multiple values (max 1024).
4. Stored procedure always returns an integer value of zero by default. Whereas function return types could be scalar or table or table values.
5. Stored procedures have a precompiled execution plan, where as functions are not.
6. A function can be called directly by SQL statement like SELECT func_name FROM DUAL while procedures cannot.
7. Stored procedure has the security and reduces the network traffic and also we can call stored procedure in any number of applications at a time.
8. A Function can be used in the SQL queries while a procedure cannot be used in SQL queries. That causes a major difference between function and procedures.

Difference between MySQL function and mysql procedure

MYSQL Function

It must return value. IN, OUT and INOUT cannot be used in function.But return datatype must be declare when create a function. function can be called from a SQL statement. Function return one values.

MYSQL Procedure

Return Values is not mandatory but may be uses the OUT parameter to procedure returns. Can use the IN | OUT | INOUT parameters. Procedure cannot be called from the SQL Statement. procedure return multiple values by using OUT or INOUT parameters.