Stored Procedures in MySQL

Most of the programmer are not using the stored procedures in any of the technologies. But it has lot of benefits and advantages. If you are frequently doing a same functionality in server-side / client-side programming we are creating a function or method to optimize the code. similar like that, if you are going to use a query frequently on your app then you should use the stored procedure / functions in the backend thats on databases.

Here is the example code in MySQL for using the stored procedures and functions:

Stored Procedures in MySQL:

Syntax

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

proc_parameter:
[ IN | OUT | INOUT ] param_name 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

Sample PHP source code

//connection here....

$query = mysql_query("CALL procedure_name(value1, value2, ....)");

Hope this will help some one or mine too!