SQL Topics
Stored Procedure Introduction
title: Stored Procedure Introduction
Until now, you have learned how to execute SQL statements such as:
SELECT
INSERT
UPDATE
DELETEindividually.
However, in real-world applications, business operations often require multiple SQL statements to execute together.
Examples:
Create Customer
Create Account
Generate Transaction Log
Send NotificationExecuting these statements repeatedly can be inefficient and difficult to manage.
To solve this problem, SQL provides:
Stored ProceduresStored Procedures allow developers to store a collection of SQL statements inside the database and execute them whenever needed.
They are one of the most powerful features of modern relational database systems and are heavily used in enterprise applications.
What is a Stored Procedure?
A Stored Procedure is a precompiled collection of SQL statements stored inside the database and executed as a single unit.
Think of it as:
Function
For Database OperationsInstead of writing the same SQL queries repeatedly:
Write Once
Store In Database
Execute Many TimesSimple Definition
A Stored Procedure is a named block of SQL code that performs one or more database operations and can be executed whenever required.
Why Were Stored Procedures Introduced?
Suppose an HR system must increase employee salaries.
Without a stored procedure:
UPDATE Employees
SET Salary = Salary + 5000
WHERE DepartmentID = 1;The same query may need to be written repeatedly.
Problems:
Code Duplication
Maintenance Difficulty
Higher Error RiskStored Procedures solve these issues.
How Stored Procedures Work
Process:
Create Procedure
↓
Store In Database
↓
Execute Procedure
↓
Perform OperationsVisual Representation
Without Stored Procedure:
Application
↓
SQL Query
↓
Database
Application
↓
SQL Query
↓
Database
Application
↓
SQL Query
↓
DatabaseRepeated code.
With Stored Procedure:
Application
↓
Procedure Call
↓
Database Procedure
↓
Execute SQLCleaner and faster.
Real-World Example
Imagine a banking system.
Every time a new customer is created:
Insert Customer
Create Account
Generate Account Number
Insert Log Recordmust occur.
Instead of writing all queries repeatedly:
Create Stored Procedureand execute it whenever needed.
Basic Stored Procedure Structure
General structure:
CREATE PROCEDURE ProcedureName
AS
BEGIN
SQL Statements
END;This creates a reusable database program.
Example Procedure
CREATE PROCEDURE GetEmployees
AS
BEGIN
SELECT *
FROM Employees;
END;Purpose:
Return All EmployeesCreating Sample Table
CREATE TABLE Employees (
EmployeeID INT,
EmployeeName VARCHAR(100),
Salary DECIMAL(10,2)
);Insert sample records:
INSERT INTO Employees VALUES
(1,'Rahul',50000),
(2,'Priya',60000),
(3,'Amit',70000);Procedure for Retrieving Data
CREATE PROCEDURE GetEmployees
AS
BEGIN
SELECT *
FROM Employees;
END;Whenever executed:
Employee List ReturnedProcedure for Updating Data
CREATE PROCEDURE IncreaseSalary
AS
BEGIN
UPDATE Employees
SET Salary = Salary + 5000;
END;Purpose:
Increase All SalariesProcedure for Inserting Data
CREATE PROCEDURE AddEmployee
AS
BEGIN
INSERT INTO Employees
VALUES
(
4,
'Neha',
80000
);
END;Purpose:
Add EmployeeProcedure for Deleting Data
CREATE PROCEDURE DeleteEmployee
AS
BEGIN
DELETE FROM Employees
WHERE EmployeeID = 4;
END;Purpose:
Remove EmployeeBenefits of Stored Procedures
Stored Procedures are widely used because they provide several advantages.
Benefit 1: Code Reusability
Write once:
Reuse Multiple TimesNo need to rewrite SQL repeatedly.
Benefit 2: Better Performance
Stored procedures are usually:
Precompiledwhich reduces execution time.
Benefit 3: Improved Security
Users may execute procedures without direct access to tables.
Example:
Allow Procedure Access
Deny Table AccessThis improves security.
Benefit 4: Easier Maintenance
Business logic remains inside the database.
Updating one procedure affects all applications using it.
Benefit 5: Reduced Network Traffic
Instead of sending multiple SQL statements:
Send One Procedure CallThis reduces communication overhead.
Stored Procedure vs SQL Query
Normal SQL Query:
Written RepeatedlyStored Procedure:
Stored Permanently
ReusableComparison:
| Feature | SQL Query | Stored Procedure |
|---|---|---|
| Reusable | No | Yes |
| Stored In Database | No | Yes |
| Precompiled | No | Usually Yes |
| Security | Lower | Better |
| Maintenance | Harder | Easier |
Stored Procedure vs Function
Stored Procedure:
Can Return Multiple Results
Can Perform Updates
Can Execute TransactionsFunction:
Usually Returns One Value
Used Inside QueriesDetailed comparison will be covered later.
Real-World Example: Banking System
Procedure:
Create Customer
Create Account
Generate Account Number
Insert Audit LogAll operations executed together.
Real-World Example: E-Commerce
Procedure:
Create Order
Update Inventory
Generate InvoiceBusiness logic centralized.
Real-World Example: Hospital Management
Procedure:
Register Patient
Assign Doctor
Generate AppointmentDatabase handles workflow.
Real-World Example: Payroll System
Procedure:
Calculate Salary
Deduct Tax
Generate PayslipAutomated processing.
Advantages of Stored Procedures
Reusable Code
Write once, use many times.
Better Performance
Precompiled execution.
Improved Security
Controlled access.
Centralized Business Logic
Easy maintenance.
Reduced Network Traffic
Fewer database requests.
Disadvantages of Stored Procedures
Database Dependency
Procedures are often database-specific.
Debugging Complexity
Large procedures can be difficult to troubleshoot.
Maintenance Challenges
Poorly designed procedures become difficult to manage.
Version Control Issues
Database code may be harder to track than application code.
Common Mistakes
Writing Extremely Large Procedures
Creates maintenance difficulties.
Mixing Too Much Business Logic
Can reduce application flexibility.
Ignoring Security Permissions
May expose sensitive operations.
Not Documenting Procedures
Makes maintenance difficult.
Best Practices
Use Meaningful Names
Example:
CreateCustomer
GenerateInvoice
CalculateSalaryKeep Procedures Focused
One procedure should perform one logical task.
Use Error Handling
Prevent unexpected failures.
Document Procedure Behavior
Improve maintainability.
Review Performance Regularly
Optimize long-running procedures.
Common Interview Questions
What is a Stored Procedure?
A stored collection of SQL statements that can be executed as a single unit.
Why are Stored Procedures used?
To improve reusability, security, and performance.
Where are Stored Procedures stored?
Inside the database.
Are Stored Procedures faster than normal queries?
Often yes, because they are usually precompiled.
Can Stored Procedures perform INSERT, UPDATE, and DELETE operations?
Yes.
They can execute almost any SQL operation.
Summary
Stored Procedures are reusable, precompiled collections of SQL statements stored inside the database. They help centralize business logic, improve performance, enhance security, and simplify application development.
In this lesson, you learned:
- What Stored Procedures are
- Why they are important
- How they work
- Procedure structure
- Real-world examples
- Benefits and drawbacks
- Procedure vs Query
- Procedure vs Function
- Best practices
Mastering Stored Procedures is essential because enterprise applications heavily rely on them for implementing secure and efficient business logic.
Next Step
Continue to the next lesson:
Create Procedure →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Stored Procedure Introduction.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, stored, procedures
Related SQL Topics