SQL Stored Procedures

A stored procedure is a precompiled collection of SQL statements that can be executed as a single unit.

1️⃣ Creating a Stored Procedure

CREATE PROCEDURE GetEmployeesByDept(IN deptId INT)
BEGIN
    SELECT name, salary
    FROM employees
    WHERE dept_id = deptId;
END;

2️⃣ Executing a Stored Procedure

CALL GetEmployeesByDept(2);

3️⃣ Stored Procedure with Output Parameter

CREATE PROCEDURE GetEmployeeCountByDept(IN deptId INT, OUT empCount INT)
BEGIN
    SELECT COUNT(*) INTO empCount
    FROM employees
    WHERE dept_id = deptId;
END;

-- Execution
CALL GetEmployeeCountByDept(2, @count);
SELECT @count;

4️⃣ Benefits of Stored Procedures

• Performance — precompiled & cached.
• Reusability — write once, use many times.
• Security — granular EXECUTE permissions.
• Maintainability — centralized business logic.

5️⃣ Limitations

• Harder to debug than app code.
• Overuse can bloat the DB layer.