Skip to content

SQL Stored Procedures 🤖

Prerequisites: SQL Commands, INSERT/UPDATE Statements

Mentor's Note: Think of a Stored Procedure like a "Microwave Preset". Instead of manually setting the power and time every time you want to pop popcorn, you just press one button. A Stored Procedure saves a complex script so you can "play" it again with a single command. 💡

🌟 The Scenario: The Daily Backup 📁

Imagine you have to move all "Cancelled Orders" to a "History" table every night. - Manual: Write 5 different queries every night. (Exhausting! 🥵) - Procedure: Write the logic once, save it as archive_orders(), and just call it. ✅

💻 1. The Basic Syntax

DELIMITER //
CREATE PROCEDURE GetAllStudents()
BEGIN
    SELECT * FROM students;
END //
DELIMITER ;

-- To Run:
CALL GetAllStudents();

CREATE PROCEDURE GetEmployeeSalary
AS
BEGIN
    SELECT emp_name, salary FROM employees;
END;

-- To Run:
EXEC GetEmployeeSalary;

💻 2. Using Parameters (Inputs) 📥

You can pass data into a procedure to make it dynamic.

-- Scenario: Create a procedure to give a specific employee a raise
CREATE PROCEDURE GiveRaise (IN emp_id INT, IN raise_amt DECIMAL)
BEGIN
    UPDATE employees 
    SET salary = salary + raise_amt 
    WHERE id = emp_id;
END;

-- To Run:
CALL GiveRaise(101, 5000);

🎨 Visual Logic: The Black Box

graph LR
    A[Input Parameters 📥] --> B[Stored Procedure ⚙️]
    B -- "Executes Script" --> C[(Database 🗄️)]
    C -- "Returns Status/Data" --> B
    B --> D[Output Result 📤]

📊 Why use Stored Procedures?

  1. Efficiency: The code is compiled once and runs faster. ⚡
  2. Security: You can let users "Run the Procedure" without giving them access to the "Raw Tables". 🛡️
  3. Consistency: Ensures the exact same math is used by every app (Web, Mobile, Admin).

📈 Learning Path