What is Stored Procedure in Database?

A Stored Procedure is a collection of SQL statements stored inside the MySQL database. Instead of sending multiple SQL queries from PHP, you store the logic in MySQL and call it whenever needed.

Think of it like this:

• View → Stores a SELECT query (virtual table).
• Stored Procedure → Stores one or more SQL statements (SELECT, INSERT, UPDATE, DELETE, IF, LOOP, etc.) that can accept parameters.

Why use Stored Procedures?

In real projects, they are used to:

• Encapsulate complex database logic.
• Reduce duplicate SQL code.
• Improve security by exposing only procedures instead of direct table access.
• Execute multiple database operations in one call.
• Handle transactions close to the data.

Example Project

Suppose you have an employees table.

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

Example 1: Procedure to Get All Employees

Create the procedure in MySQL:

DELIMITER $$

CREATE PROCEDURE GetEmployees()
BEGIN
    SELECT * FROM employees;
END $$

DELIMITER ;

Run this once in phpMyAdmin or MySQL Workbench.

CALL GetEmployees();

$conn = new mysqli("localhost", "root", "", "company");

$result = $conn->query("CALL GetEmployees()");

while ($row = $result->fetch_assoc()) {
    echo $row['name'] . "<br>";
}

DELIMITER $$

CREATE PROCEDURE GetDepartmentEmployees(IN dept VARCHAR(50))
BEGIN
    SELECT * FROM employees
    WHERE department = dept;
END $$

DELIMITER ;

Get employees from a specific department.

CALL GetDepartmentEmployees('IT');

$department = "IT";

$result = $conn->query("CALL GetDepartmentEmployees('$department')");

while ($row = $result->fetch_assoc()) {
    echo $row['name'];
}

DELIMITER $$

CREATE PROCEDURE AddEmployee(
    IN empName VARCHAR(100),
    IN empDept VARCHAR(50),
    IN empSalary DECIMAL(10,2)
)
BEGIN
    INSERT INTO employees(name, department, salary)
    VALUES(empName, empDept, empSalary);
END $$

DELIMITER ;

$conn->query("CALL AddEmployee('Amit', 'HR', 45000)");

DELIMITER $$

CREATE PROCEDURE UpdateSalary(
    IN empId INT,
    IN newSalary DECIMAL(10,2)
)
BEGIN
    UPDATE employees
    SET salary = newSalary
    WHERE id = empId;
END $$

DELIMITER ;

$conn->query("CALL UpdateSalary(5, 70000)");