Stored Procedure in SQL

A stored procedure is a precompiled group of one or more SQL statements stored in the database. It allows you to encapsulate logic, making database operations reusable, faster, and easier to maintain.

Features of Stored Procedures

• Written using SQL + procedural constructs (loops, conditions, etc.)
• Can accept parameters (IN, OUT, INOUT)
• Can return result sets or output values
• Improves performance (precompiled)
• Enhances security and code reuse

Basic Syntax (MySQL-style)

DELIMITER $$

CREATE PROCEDURE procedure_name (param1 datatype, param2 datatype, ...)
BEGIN
   -- SQL statements
END$$

DELIMITER ;

Example: Simple Stored Procedure

Task: Create a procedure to insert a new employee into the Employees table.

Table:

CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(50),
    Salary DECIMAL(10,2)
);

Procedure:

DELIMITER $$

CREATE PROCEDURE AddEmployee (
    IN emp_id INT,
    IN emp_name VARCHAR(50),
    IN emp_salary DECIMAL(10,2)
)
BEGIN
    INSERT INTO Employees(EmpID, EmpName, Salary)
    VALUES (emp_id, emp_name, emp_salary);
END$$

DELIMITER ;

Calling the Procedure

CALL AddEmployee(101, 'Meera', 50000);

This inserts a new employee into the Employees table.

Advantages of Stored Procedures