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

Question

In MySQL database context, what is the difference among these 3 terms:

• stored procedure
• stored function
• stored routine

Also the build-in functions like those date time functions (e.g. WEEKDAY() etc) are considered as what?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

PROCEDURES VS FUNCTIONS

1.  PROCEDURES may or may not return a value but FUNCTION must return a value
2.  PROCEDURES can have input/output parameter but FUNCTION only has input parameter.
3.  We can call FUNCTION from PROCEDURES but cannot call PROCEDURES from a function. 
4.  We cannot use PROCEDURES in SQL statement like SELECT, INSERT, UPDATE, DELETE, MERGE etc. but we can use them with FUNCTION.
5.  We can use try-catch exception handling in PROCEDURES but we cannot do that in FUNCTION. 
6.  We can use transaction in PROCEDURES but it is not possible in FUNCTION.

Answer 5

Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values). Functions can have only input parameters for it whereas Procedures can have input/output parameters . Functions can be called from Procedure whereas Procedures cannot be called from Function.