SQL - Stored Procedures in SQL

A stored procedure is a pre-defined group of SQL statements that is stored inside a database and can be executed whenever required. Instead of writing the same SQL statements repeatedly, a stored procedure allows developers to save those statements under a specific name and execute them by calling that name. Stored procedures are commonly used to perform repetitive database operations, apply business rules, validate data, and simplify application development.

What Is a Stored Procedure?

A stored procedure is a database object that contains one or more SQL statements. It can accept input parameters, perform operations on database tables, and, depending on the database system, return output values or result sets.

For example, suppose a company frequently needs to retrieve employee details based on department. Instead of writing the same SELECT query every time, a stored procedure can be created:

CREATE PROCEDURE GetEmployeesByDepartment
    @DepartmentID INT
AS
BEGIN
    SELECT EmployeeID, EmployeeName, Salary
    FROM Employees
    WHERE DepartmentID = @DepartmentID;
END;

The procedure can then be executed by providing a department ID:

EXEC GetEmployeesByDepartment 10;

The database executes the SQL statements stored inside the procedure and returns the corresponding employee records.

Why Are Stored Procedures Used?

Stored procedures are useful when the same database operation needs to be performed repeatedly. They reduce the need to send long SQL statements from an application every time an operation is required.

For example, an application may need to perform the following operations whenever a new employee joins:

  1. Insert employee information.

  2. Create an employee account.

  3. Record the employee's department.

  4. Add an entry to an employee history table.

These operations can be combined into a stored procedure. The application can then call the procedure rather than sending each SQL statement individually.

Basic Structure of a Stored Procedure

The exact syntax varies between database systems such as SQL Server, MySQL, PostgreSQL, and Oracle. A general SQL Server-style structure is:

CREATE PROCEDURE ProcedureName
    @Parameter1 DataType,
    @Parameter2 DataType
AS
BEGIN
    SQL statements;
END;

For example:

CREATE PROCEDURE GetStudent
    @StudentID INT
AS
BEGIN
    SELECT StudentID, StudentName, Course
    FROM Students
    WHERE StudentID = @StudentID;
END;

Here, GetStudent is the procedure name and @StudentID is an input parameter.

Input Parameters

Stored procedures can receive values from the user or application through parameters. Parameters make procedures flexible because the same procedure can work with different values.

For example:

CREATE PROCEDURE GetProduct
    @ProductID INT
AS
BEGIN
    SELECT ProductID, ProductName, Price
    FROM Products
    WHERE ProductID = @ProductID;
END;

The procedure can be called for different products:

EXEC GetProduct 101;

or:

EXEC GetProduct 205;

The procedure remains the same, but the supplied parameter changes the result.

Output Parameters

Some database systems allow stored procedures to return values through output parameters. These parameters are useful when the application needs a specific calculated value rather than an entire result set.

For example:

CREATE PROCEDURE GetEmployeeCount
    @DepartmentID INT,
    @TotalEmployees INT OUTPUT
AS
BEGIN
    SELECT @TotalEmployees = COUNT(*)
    FROM Employees
    WHERE DepartmentID = @DepartmentID;
END;

The procedure calculates the number of employees in a particular department and places the result in the output parameter.

Stored Procedures for Insert Operations

Stored procedures can also be used to insert records into a table.

CREATE PROCEDURE AddStudent
    @StudentName VARCHAR(100),
    @Course VARCHAR(100)
AS
BEGIN
    INSERT INTO Students (StudentName, Course)
    VALUES (@StudentName, @Course);
END;

The procedure can be executed as:

EXEC AddStudent 'Rahul', 'Computer Science';

This adds a new student to the Students table.

Stored Procedures for Updating Data

A stored procedure can contain an UPDATE statement to modify existing records.

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

It can be called using:

EXEC UpdateStudentCourse 101, 'Information Technology';

The course of student 101 will be updated.

Stored Procedures for Deleting Data

Stored procedures can also be used to delete records.

CREATE PROCEDURE DeleteStudent
    @StudentID INT
AS
BEGIN
    DELETE FROM Students
    WHERE StudentID = @StudentID;
END;

The procedure can be executed using:

EXEC DeleteStudent 101;

This removes the specified student record.

Conditional Logic in Stored Procedures

Stored procedures can contain conditional statements. This allows the procedure to make decisions based on the supplied data.

For example:

CREATE PROCEDURE CheckSalary
    @EmployeeID INT
AS
BEGIN
    DECLARE @Salary DECIMAL(10,2);

    SELECT @Salary = Salary
    FROM Employees
    WHERE EmployeeID = @EmployeeID;

    IF @Salary >= 50000
        PRINT 'Salary is above the specified level';
    ELSE
        PRINT 'Salary is below the specified level';
END;

The procedure first retrieves the employee's salary and then performs different actions depending on the salary amount.

Advantages of Stored Procedures

Stored procedures provide several benefits.

Code Reusability:
A stored procedure can be created once and executed many times. This prevents developers from repeatedly writing the same SQL statements.

Improved Maintainability:
Database operations can be maintained in one central location. If the logic needs to change, the stored procedure can be modified instead of changing the same SQL code in multiple application locations.

Reduced Network Traffic:
Instead of sending multiple SQL statements from an application to the database, an application can call a single stored procedure. This can reduce communication between the application and database.

Security:
Stored procedures can help control access to database operations. Users may be granted permission to execute a procedure without being given direct permission to modify certain tables.

Centralized Business Logic:
Some database-related rules can be implemented within stored procedures so that different applications accessing the same database can use the same logic.

Disadvantages of Stored Procedures

Stored procedures also have some limitations.

Database Dependency:
Stored procedures often use database-specific syntax. A procedure written for SQL Server may require significant changes before it can be used with another database system.

Maintenance Complexity:
Large and complicated stored procedures can become difficult to understand and maintain.

Testing Challenges:
Testing database-side logic may require specialized database testing approaches in addition to normal application testing.

Version Management:
If stored procedures are not properly managed through source control and deployment processes, keeping database code synchronized with application code can become difficult.

Stored Procedure vs Normal SQL Query

A normal SQL query is generally written and executed whenever an operation is required.

For example:

SELECT *
FROM Employees
WHERE DepartmentID = 10;

A stored procedure saves the operation inside the database:

CREATE PROCEDURE GetEmployees
    @DepartmentID INT
AS
BEGIN
    SELECT *
    FROM Employees
    WHERE DepartmentID = @DepartmentID;
END;

The procedure can then be executed whenever needed:

EXEC GetEmployees 10;

The main difference is that a stored procedure is a reusable database object containing stored SQL logic, whereas a normal query is typically submitted directly for execution.

Real-World Example

Consider an online shopping system. When a customer places an order, several database operations may be required:

  1. Add the order to the orders table.

  2. Add the purchased products to the order-details table.

  3. Reduce product stock.

  4. Calculate the order total.

  5. Record the transaction.

Instead of allowing the application to independently execute all these operations, a stored procedure can be designed to handle the required database operations in a controlled manner.

For example:

CREATE PROCEDURE PlaceOrder
    @CustomerID INT,
    @ProductID INT,
    @Quantity INT
AS
BEGIN
    INSERT INTO Orders (CustomerID, ProductID, Quantity)
    VALUES (@CustomerID, @ProductID, @Quantity);

    UPDATE Products
    SET Stock = Stock - @Quantity
    WHERE ProductID = @ProductID;
END;

The application can call the procedure whenever an order is placed.

Important Consideration

Stored procedure syntax is not identical across all SQL database systems. SQL Server uses constructs such as CREATE PROCEDURE, EXEC, and @Parameter, while MySQL, PostgreSQL, and Oracle have their own syntax and procedural extensions. Therefore, when learning stored procedures, it is important to identify which database system is being used.

Conclusion

Stored procedures are an important database programming feature that allows multiple SQL statements and database logic to be stored under a reusable procedure name. They can accept parameters, retrieve data, insert or modify records, perform conditional operations, and sometimes return output values. They are particularly useful for repetitive database operations, centralized database logic, security control, and application-database integration. Understanding stored procedures provides a foundation for developing more organized and reusable database applications.