SQL Topics
Stored Procedures
title: Stored Procedures
As database applications grow larger, developers often need to execute the same SQL statements repeatedly.
For example:
- Adding a new employee
- Generating monthly reports
- Updating salaries
- Processing customer orders
- Calculating account balances
Writing the same SQL code repeatedly can lead to:
Code Duplication
Maintenance Problems
Security Risks
Higher Development TimeTo solve these issues, SQL provides Stored Procedures.
A Stored Procedure is a precompiled collection of SQL statements stored inside the database. It can be executed whenever needed, just like calling a function in a programming language.
Stored Procedures improve performance, reusability, security, and maintainability.
What is a Stored Procedure?
A Stored Procedure is a named block of SQL code stored in the database.
Think of it as:
Reusable SQL ProgramOnce created:
Create Once
↓
Store In Database
↓
Execute Many Timeswithout rewriting the SQL statements.
Why Are Stored Procedures Important?
Suppose an HR system frequently needs to:
Insert Employee
Update Salary
Generate ReportsWithout Stored Procedures:
Application Sends
Large SQL Queries
RepeatedlyWith Stored Procedures:
EXEC AddEmployee;The application simply calls the procedure.
Key Characteristics of Stored Procedures
A Stored Procedure:
✅ Stored inside the database
✅ Reusable
✅ Can accept parameters
✅ Can return results
✅ Supports conditional logic
✅ Improves security
✅ Reduces network traffic
How Stored Procedures Work
Process:
Create Procedure
↓
Store In Database
↓
Application Calls Procedure
↓
Procedure Executes
↓
Returns ResultThe SQL code remains inside the database.
Basic Stored Procedure Syntax
SQL Server Example
CREATE PROCEDURE ProcedureName
AS
BEGIN
SQL Statements
END;Example:
CREATE PROCEDURE GetEmployees
AS
BEGIN
SELECT *
FROM Employees;
END;Executing a Stored Procedure
SQL Server:
EXEC GetEmployees;or
EXECUTE GetEmployees;Result:
All Employee Recordsare returned.
Creating Sample Table
CREATE TABLE Employees (
EmployeeID INT,
EmployeeName VARCHAR(100),
Salary DECIMAL(10,2)
);Insert records:
INSERT INTO Employees VALUES
(1, 'Rahul', 50000),
(2, 'Priya', 60000),
(3, 'Amit', 70000);First Stored Procedure Example
Create:
CREATE PROCEDURE GetEmployees
AS
BEGIN
SELECT *
FROM Employees;
END;Execute:
EXEC GetEmployees;Output:
| EmployeeID | EmployeeName | Salary |
|---|---|---|
| 1 | Rahul | 50000 |
| 2 | Priya | 60000 |
| 3 | Amit | 70000 |
Stored Procedure with Parameters
Parameters make procedures dynamic.
Example:
CREATE PROCEDURE GetEmployeeByID
@EmployeeID INT
AS
BEGIN
SELECT *
FROM Employees
WHERE EmployeeID =
@EmployeeID;
END;Execute:
EXEC GetEmployeeByID 2;Output:
| EmployeeID | EmployeeName |
|---|---|
| 2 | Priya |
Multiple Parameters
Example:
CREATE PROCEDURE AddEmployee
@EmployeeID INT,
@EmployeeName VARCHAR(100),
@Salary DECIMAL(10,2)
AS
BEGIN
INSERT INTO Employees
VALUES
(
@EmployeeID,
@EmployeeName,
@Salary
);
END;Execute:
EXEC AddEmployee
4,
'Neha',
80000;Stored Procedure with UPDATE
Example:
CREATE PROCEDURE UpdateSalary
@EmployeeID INT,
@NewSalary DECIMAL(10,2)
AS
BEGIN
UPDATE Employees
SET Salary = @NewSalary
WHERE EmployeeID =
@EmployeeID;
END;Execute:
EXEC UpdateSalary
1,
55000;Stored Procedure with DELETE
Example:
CREATE PROCEDURE DeleteEmployee
@EmployeeID INT
AS
BEGIN
DELETE FROM Employees
WHERE EmployeeID =
@EmployeeID;
END;Execute:
EXEC DeleteEmployee 3;Stored Procedure with IF Condition
Example:
CREATE PROCEDURE CheckSalary
@EmployeeID INT
AS
BEGIN
DECLARE @Salary DECIMAL(10,2);
SELECT @Salary = Salary
FROM Employees
WHERE EmployeeID =
@EmployeeID;
IF @Salary > 60000
PRINT 'High Salary';
ELSE
PRINT 'Normal Salary';
END;Stored Procedure with Variables
Example:
CREATE PROCEDURE EmployeeCount
AS
BEGIN
DECLARE @Total INT;
SELECT @Total =
COUNT(*)
FROM Employees;
PRINT @Total;
END;Variables help perform intermediate calculations.
Stored Procedure with Aggregate Functions
Example:
CREATE PROCEDURE AverageSalary
AS
BEGIN
SELECT AVG(Salary)
FROM Employees;
END;Returns:
Average Employee SalaryStored Procedure Returning Result Sets
Example:
CREATE PROCEDURE HighSalaryEmployees
AS
BEGIN
SELECT *
FROM Employees
WHERE Salary > 60000;
END;Result set behaves like a normal SELECT query.
Stored Procedure Returning Output Parameters
Example:
CREATE PROCEDURE EmployeeTotal
@TotalEmployees INT OUTPUT
AS
BEGIN
SELECT @TotalEmployees =
COUNT(*)
FROM Employees;
END;Usage:
DECLARE @Count INT;
EXEC EmployeeTotal
@Count OUTPUT;
PRINT @Count;Real-World Example: HR System
Procedure:
Add Employee
Update Employee
Delete Employee
Generate ReportsStored procedures centralize business logic.
Real-World Example: Banking
Procedure:
Transfer Money
Withdraw Funds
Deposit Funds
Check BalanceCritical business operations are often implemented using stored procedures.
Real-World Example: E-Commerce
Procedure:
Place Order
Update Inventory
Calculate Discounts
Generate Sales ReportsStored procedures automate these processes.
Stored Procedure vs SQL Query
Normal Query:
SELECT *
FROM Employees;Executed directly.
Stored Procedure:
EXEC GetEmployees;Stored and reusable.
Comparison Table
| Feature | SQL Query | Stored Procedure |
|---|---|---|
| Reusable | No | Yes |
| Stored In Database | No | Yes |
| Parameters | Limited | Yes |
| Business Logic | Limited | Advanced |
| Security | Lower | Higher |
Stored Procedure vs Function
Stored Procedure:
Can Perform
INSERT
UPDATE
DELETE
SELECTFunction:
Primarily Returns
A ValueFunctions are covered in the next lesson.
Advantages of Stored Procedures
Reusability
Write once, use many times.
Better Performance
Procedures are often precompiled and optimized.
Improved Security
Users can execute procedures without direct table access.
Reduced Network Traffic
Only procedure calls are sent.
Easier Maintenance
Business logic is centralized.
Disadvantages of Stored Procedures
Database Dependency
Syntax varies between database systems.
Debugging Complexity
Large procedures may become difficult to debug.
Maintenance Challenges
Poorly designed procedures can become difficult to manage.
Version Control Issues
Database code requires separate management.
Database Support
SQL Server
CREATE PROCEDUREMySQL
CREATE PROCEDURESupported.
PostgreSQL
Supports procedures and functions.
Oracle
Uses PL/SQL procedures extensively.
Performance Considerations
Stored procedures can improve performance because:
Execution Plans
May Be Reusedand:
Less Network Communicationis required.
However:
Poorly Written Procedures
Can Still Be SlowCommon Errors
Forgetting Parameters
Wrong:
EXEC GetEmployeeByID;Required parameter missing.
Incorrect Data Types
Parameter types must match.
Infinite Loops
Improper loops can cause issues.
Hardcoding Values
Avoid fixed values when parameters are appropriate.
Best Practices
Use Meaningful Names
Example:
GetEmployees
UpdateSalary
GenerateReportKeep Procedures Focused
One business task per procedure.
Validate Inputs
Check parameter values.
Avoid Excessive Complexity
Break large procedures into smaller ones.
Document Procedure Logic
Helps future maintenance.
Common Interview Questions
What is a Stored Procedure?
A reusable block of SQL code stored inside the database.
Why use Stored Procedures?
For performance, security, and reusability.
Can Stored Procedures accept parameters?
Yes.
Input and output parameters are supported.
Can Stored Procedures modify data?
Yes.
They can perform INSERT, UPDATE, and DELETE operations.
Are Stored Procedures faster than normal queries?
Often yes, but performance depends on implementation.
Summary
Stored Procedures are reusable SQL programs stored inside the database. They help centralize business logic, improve security, reduce code duplication, and automate common database operations.
In this lesson, you learned:
- What Stored Procedures are
- Why they are important
- Creating procedures
- Executing procedures
- Parameters
- Output parameters
- Variables
- Conditional logic
- Real-world applications
- Performance considerations
- Best practices
Mastering Stored Procedures is essential because they are widely used in enterprise applications, banking systems, ERP software, e-commerce platforms, and large-scale database solutions.
Next Step
Continue to the next lesson:
Functions in SQL →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Stored Procedures.
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, advanced, Stored
Related SQL Topics