When learning SQL, you may hear the term SQL Stored Procedure. Many freshers get confused: Is it a function? Is it a query? Don’t worry — in this blog, we will explain what a stored procedure is, why we use it, and how it works with simple examples.
What is a Stored Procedure?
- A Stored Procedure is a set of SQL statements that is saved in the database and can be run whenever needed.
- Instead of writing the same query again and again, you save it once as a procedure and just call it.
- Think of it like a recipe card in a kitchen. You write the recipe once and use it many times.
Example:
CREATE PROCEDURE GetHighScorers()
BEGIN
SELECT name, marks
FROM Students
WHERE marks > 75;
END;
Now, instead of writing the SELECT query again and again, you just run:
CALL GetHighScorers();
Why Use Stored Procedures?
- Reusability – Write once, use many times.
- Saves Time – No need to type long SQL queries repeatedly.
- Security – Users can run the procedure without seeing the raw SQL.
- Performance – Stored procedures are pre-compiled, so they run faster.
Quick Analogy
Imagine a canteen in your college:
- Instead of the chef writing a recipe every time, he keeps a ready recipe card (stored procedure).
- Whenever a student orders, he just follows the saved recipe.
Same way, SQL saves queries as a stored procedure.
Simple Examples
- Insert Data Using Procedure
CREATE PROCEDURE AddStudent(IN s_name VARCHAR(50), IN s_marks INT)
BEGIN
INSERT INTO Students(name, marks) VALUES(s_name, s_marks);
END;
Call it like this:
CALL AddStudent('Ravi', 88);
- Update Data Using Procedure
CREATE PROCEDURE UpdateMarks(IN s_id INT, IN new_marks INT)
BEGIN
UPDATE Students SET marks = new_marks WHERE id = s_id;
END;
Call it like this:
CALL UpdateMarks(1, 95);
Conclusion
- A Stored Procedure in SQL is like a saved recipe of queries.
- It helps in saving time, improving performance, and increasing security.
- Freshers should practice creating small stored procedures (Insert, Update, Select) to understand them better.