Today, a majority of databases support stored procedures. A stored procedure is simply a set of SQL instructions that perform a particular task which is stored in the database to help associated programming languages and database engine retrieve it anytime. Stored procedures are a great way to increase the performance and reduce network traffic. They usually accept input parameters and can be used by different clients over the network.
 

Why stored procedures are used?

There are many benefits of using stored procedures and some of them are discussed below:

Modular programming:

Stored procedure enables modular programming. You can always create a procedure for recurrent tasks and simply call it as many times as you need it.

Faster execution

Stored procedures are usually created to remove the requirement of writing the same code again and again. If the process needs a set of SQL instructions to perform repetitively, stored procedures always come handy. These stored procedures are optimized and parsed when they are called for the first time, and the complied version stays in the memory cache to use it afterwards.

Reduces network traffic

A task requiring a set of SQL instructions can be performed by calling a stored procedure. All you need is a single statement that calls a stored procedure instead of writing hundreds of statements again and again over the network.

Better security

Stored procedures can be executed depending on the permissions assigned to the users. No user can use them unless they do not have the permissions to access it.

Types of stored procedures:

There are basically three types of stored procedures.

System

The system stored procedures, as the name suggests, are used for systems. They are saved in the master database and usually start with a sp_ prefix. These procedures are used to perform many different tasks such as supporting SQL server functions.

User defined

These types of stored procedures are stored in the user database and are created to perform a variety of tasks in the same database. They start with sp_ prefix since it first checks the master database before checking the user database.

Extended

These types of procedures call functions from DLL files. However, a majority of programmers have depreciated the use of extended stored procedures.

Creating a procedure in MySQL
CREATE PROCEDURE p(IN id_val INT)
BEGIN
INSERT INTO test(id) VALUES(id_val);
END;

Stored procedures accept three parameters – IN, INOUT and OUT. IN parameter can be called with CALL statement.

Calling a procedure using PHP
 

<?php
$mysqli = new mysqli("test.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!$mysqli->query("CALL p(1)")) {
echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!($res = $mysqli->query("SELECT id FROM test"))) {
echo "SELECT failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

var_dump($res->fetch_assoc());
?>
 

INOUT and OUT parameters:

The values of these types of parameters are accessed using session variables.
 

<?php
$mysqli = new mysqli("test.com", "user", "password", "database");
if ($mysqli->connect_errno) {

echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("SET @msg = ''") || !$mysqli->query("CALL p(@msg)")) {
echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!($res = $mysqli->query("SELECT @msg as _p_out"))) {
echo "Fetch failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

$row = $res->fetch_assoc();
echo $row['_p_out'];
?>
Handling result sets in stored procedures
 

<?php
$mysqli = new mysqli("test.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT)") ||
    !$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)")) {
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!$mysqli->multi_query("CALL p()")) {
    echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

do {
    if ($res = $mysqli->store_result()) {
        printf("---\n");
        var_dump($res->fetch_all());
        $res->free();
    } else {
        if ($mysqli->errno) {
            echo "Store failed: (" . $mysqli->errno . ") " . $mysqli->error;
        }
    }
} while ($mysqli->more_results() && $mysqli->next_result());
?>