Loading
JAVA
SPRING
ANGULAR
SQL
HTML
CSS
JAVASCRIPT
Data Structure
Procedures
VideosDiscussion
As applications grow, database logic often becomes:

  • Repetitive across multiple queries
  • Embedded directly in application code
  • Hard to maintain and error-prone
Imagine writing the same SQL logic again and again in different parts of an application.
Any change would require updating that logic everywhere.

To solve this problem, databases provide stored procedures. Stored procedures allow us to store SQL logic inside the database and reuse it whenever needed.


What Is a Stored Procedure?

A stored procedure is:

  • A named block of SQL statements
  • Stored permanently in the database
  • Executed whenever it is called
  • Capable of accepting input parameters

In simple words:
A stored procedure is a reusable SQL program stored inside the database.


Why Use Stored Procedures?

Stored procedures are widely used because they:

  • Reduce code duplication
  • Improve maintainability
  • Enhance performance
  • Improve security by limiting direct table access
  • Encapsulate complex business logic
They act as a bridge between application logic and database logic.


Basic Structure of a Stored Procedure

Although syntax varies slightly between databases, the core idea remains the same.

CREATE PROCEDURE procedure_name
AS
BEGIN
    SQL statements
END;


Example: A Basic Stored Procedure

CREATE PROCEDURE get_all_employees
AS
BEGIN
    SELECT * FROM employees;
END;

How This Works

  • The procedure is stored in the database
  • No data is stored inside the procedure
  • It can be executed whenever needed


Executing a Stored Procedure

EXEC get_all_employees;

The database runs the stored SQL and returns the result.


Stored Procedures with Input Parameters

Stored procedures become more powerful when they accept parameters.


Example: Get Employees by Department

CREATE PROCEDURE get_employees_by_department
    @dept_id INT
AS
BEGIN
    SELECT emp_name, salary
    FROM employees
    WHERE department_id = @dept_id;
END;


Executing the Procedure

EXEC get_employees_by_department 10;

This makes the procedure flexible and reusable.


Why Parameters Are Important

Parameters allow:

  • Dynamic input
  • Reusable logic
  • Safer queries
  • Cleaner application code
They eliminate the need to rewrite queries with different values.


Difference Between Stored Procedures and Normal SQL Queries

FeatureStored ProcedureNormal Query
ReusabilityHighLow
PerformanceBetter (precompiled)Slower
SecurityStrongWeaker
MaintenanceCentralizedScattered


Stored Procedures and Performance

Stored procedures often perform better because:

  • Execution plans can be reused
  • Network traffic is reduced
  • Complex logic runs inside the database
However, performance gains depend on proper design.


Security Benefits of Stored Procedures

Stored procedures:

  • Hide table structure
  • Restrict direct table access
  • Allow controlled operations

Example:

  • Users can execute a procedure
  • But cannot directly access tables
This is widely used in enterprise systems. 


Common Use Cases in Real-World Systems

Stored procedures are commonly used for:

  • Business logic enforcement
  • Data validation
  • Batch processing
  • Reporting logic
  • Data transformation


Two Minute Drill

  • Stored procedures store SQL logic in the database
  • They are reusable and executable
  • Parameters make procedures flexible
  • Improve performance and security
  • Widely used in enterprise applications