SQL

Stored Procedures in SQL

⚙️ A Stored Procedure is a precompiled collection of one or more SQL statements stored in the database and executed as a single unit. Stored procedures help automate repetitive tasks, improve code reusability, enhance security, and simplify database application development.

📖 What is a Stored Procedure?

A stored procedure is a named database object that contains SQL statements and optional programming logic such as variables, conditions, loops, and error handling (depending on the database system). Once created, it can be executed whenever needed without rewriting the SQL code.

Information

Stored procedures are supported by most major relational database systems, although the syntax and procedural language differ between platforms such as SQL Server, MySQL, PostgreSQL, and Oracle.

🎯 Why Use Stored Procedures?

Stored procedures provide a structured and efficient way to manage database operations.

📌 Reuse SQL logic across multiple applications.
📌 Reduce duplicate SQL code.
📌 Improve performance by reusing execution plans where supported.
📌 Increase security by granting access to procedures instead of tables.
📌 Encapsulate complex business logic.
📌 Simplify database maintenance.

📋 Sample Table

StudentID	Name	Department	Age
101	Alice	Computer Science	20
102	Bob	Mathematics	21
103	Charlie	Physics	19

📝 Basic Syntax

The exact syntax depends on the database system. The following example uses SQL Server syntax.

CREATE PROCEDURE Syntax (SQL Server)

CREATE PROCEDURE procedure_name
AS
BEGIN
    -- SQL statements
END;

💡 Create a Simple Stored Procedure

Create a procedure that returns all student records.

Get All Students

CREATE PROCEDURE GetStudents
AS
BEGIN
    SELECT *
    FROM Students;
END;

▶️ Execute a Stored Procedure

After creating the procedure, execute it whenever the data is needed.

Execute Procedure

EXEC GetStudents;

🎯 Stored Procedure with Parameters

Parameters allow a procedure to accept input values, making it more flexible.

Procedure with Input Parameter

CREATE PROCEDURE GetStudentByDepartment
    @Department VARCHAR(100)
AS
BEGIN
    SELECT *
    FROM Students
    WHERE Department = @Department;
END;

▶️ Execute with a Parameter

Execute Parameterized Procedure

EXEC GetStudentByDepartment
    @Department = 'Computer Science';

➕ Stored Procedure for INSERT

Stored procedures are commonly used to insert data while centralizing business rules.

Insert Student

CREATE PROCEDURE AddStudent
    @Name VARCHAR(100),
    @Department VARCHAR(100),
    @Age INT
AS
BEGIN
    INSERT INTO Students
    (Name, Department, Age)
    VALUES
    (@Name, @Department, @Age);
END;

▶️ Execute the INSERT Procedure

Add a Student

EXEC AddStudent
    @Name = 'David',
    @Department = 'Physics',
    @Age = 22;

🔄 Stored Procedure for UPDATE

Update Student Department

CREATE PROCEDURE UpdateDepartment
    @StudentID INT,
    @Department VARCHAR(100)
AS
BEGIN
    UPDATE Students
    SET Department = @Department
    WHERE StudentID = @StudentID;
END;

📊 Procedure Workflow

Step	Description
Create	Define and store the procedure in the database.
Execute	Call the procedure when needed.
Run SQL	The database executes the stored SQL statements.
Return Result	Results or status information are returned to the caller.

⚖️ Stored Procedure vs SQL Query

Feature	Stored Procedure	Regular SQL Query
Stored in Database	✅ Yes	❌ No
Reusable	✅ Yes	Limited
Accept Parameters	✅ Yes	Typically No
Contains Business Logic	✅ Yes	Usually Limited
Executed on Demand	✅ Yes	✅ Yes

💼 Real-World Example

A banking application uses a stored procedure to transfer money between two accounts. The procedure validates account balances, updates both accounts, and records the transaction, ensuring the entire operation follows the required business rules.

Conceptual Transfer Procedure

CREATE PROCEDURE TransferFunds
    @FromAccount INT,
    @ToAccount INT,
    @Amount DECIMAL(10,2)
AS
BEGIN
    -- Validate balance
    -- Debit source account
    -- Credit destination account
    -- Record transaction
END;

🗄️ Database Compatibility

Database System	Stored Procedure Support
SQL Server	✅ Full support using T-SQL.
MySQL	✅ Supports stored procedures.
PostgreSQL	✅ Supports procedures (introduced in PostgreSQL 11) in addition to functions.
Oracle	✅ Full support using PL/SQL.
SQLite	❌ Does not support stored procedures.

⚠️ Advantages

✅ Centralizes business logic.
✅ Improves code reuse.
✅ Reduces network traffic by executing multiple statements together.
✅ Simplifies application development.
✅ Can improve security through controlled access.

⚠️ Limitations

❌ Syntax differs across database systems.
❌ Large procedures can become difficult to maintain.
❌ Business logic tied closely to the database can reduce portability.
❌ Debugging may be more challenging than application code.

Warning

Avoid placing excessive business logic into a single stored procedure. Large, complex procedures are harder to test, maintain, and optimize.

⚠️ Best Practices

Best Practice

Keep stored procedures focused on a single responsibility, use meaningful names, validate input parameters, implement proper error handling and transactions where appropriate, document procedure behavior, and grant execution permissions instead of direct table access whenever possible.

🚀 Key Points to Remember

📌 Stored procedures are reusable collections of SQL statements.
📌 They can accept input parameters.
📌 They simplify complex database operations.
📌 They can improve security and code organization.
📌 Syntax and capabilities vary between database systems.
📌 They are widely used for business logic, reporting, and data manipulation.

>>"A well-designed stored procedure keeps database logic organized, reusable, and secure."

Summary

✅ Stored procedures are powerful database objects that encapsulate reusable SQL logic. They help simplify application development, improve maintainability, support parameterized operations, and centralize business rules. Although their syntax varies across database systems, stored procedures remain an essential feature for building scalable and secure database applications.