SQL Stored Procedures

A stored procedure is a set of SQL statements that can be stored and executed on a database server. These procedures are typically used to encapsulate repetitive or complex operations, allowing them to be reused multiple times by different programs and applications. Stored procedures can accept parameters, return results, and be called directly from client applications.

Benefits of Stored Procedures

1. Performance:

   • Stored procedures are precompiled and stored in the database, which can lead to faster execution times compared to sending individual SQL queries from an application.
   • They reduce the amount of data sent between the application and the database server, which can minimize network traffic.

2. Security:

   • By encapsulating the database logic within stored procedures, you can control access to the data and ensure that users can only execute the procedures and not directly manipulate the tables.
   • Parameters can be used to prevent SQL injection attacks.

3. Maintainability:

   • Centralizing the SQL logic in stored procedures makes it easier to maintain and update the code. Changes can be made in one place rather than updating multiple application codebases.
   • It also promotes code reuse and consistency across different applications.

4. Transaction Management:

   • Stored procedures can handle complex transactions, ensuring that a series of SQL statements are executed as a single unit of work, which can be rolled back if any part of the transaction fails.

Creating and Using Stored Procedures

Syntax for Creating a Stored Procedure

The syntax for creating a stored procedure can vary slightly between different database systems (e.g., MySQL, SQL Server, Oracle). Below is an example of creating a simple stored procedure in MySQL:

CREATE PROCEDURE GetEmployeeDetails (IN empID INT)
BEGIN 
SELECT * FROM Employees WHERE EmployeeID = empID; 
END; 

In this example, GetEmployeeDetails is a stored procedure that takes an employee ID as an input parameter and retrieves the corresponding employee details from the Employees table.

Executing a Stored Procedure

To execute the stored procedure, you use the CALL statement in MySQL:

CALL GetEmployeeDetails(101); 

This will call the GetEmployeeDetails procedure with the employee ID 101.

Advanced Features of Stored Procedures

1. Parameters:

   • Stored procedures can accept input parameters (IN), output parameters (OUT), and input-output parameters (INOUT).
   • These parameters allow for greater flexibility and interactivity in the operations performed by the procedure.

   DELIMITER //
   
   CREATE PROCEDURE CalculateBonus (IN empID INT, OUT bonus DECIMAL(10,2), INOUT totalSalary DECIMAL(10,2))
   BEGIN
       DECLARE baseSalary DECIMAL(10,2);
   
       -- Retrieve the base salary
       SELECT Salary INTO baseSalary FROM Employees WHERE EmployeeID = empID;
   
       -- Calculate the bonus as 10% of the base salary
       SET bonus = baseSalary * 0.10;
   
       -- Add the bonus to the total salary
       SET totalSalary = totalSalary + bonus;
   END //
   
   DELIMITER ;
   
   CALL CalculateBonus(101, @bonus, @totalSalary);
   SELECT @bonus, @totalSalary;
   
   

This procedure takes an employee ID as input (IN), calculates a bonus (10% of the base salary) as an output (OUT), and updates the total salary with the calculated bonus (INOUT).

1. Error Handling:

   • Stored procedures can include error handling mechanisms using constructs like TRY...CATCH (in SQL Server) or DECLARE ... HANDLER (in MySQL) to manage exceptions and ensure robust operation.

2. Conditional Logic:

   • Stored procedures can contain conditional statements (IF...ELSE, CASE) and loops (WHILE, LOOP) to perform more complex logic and iterative operations.

   
   CREATE PROCEDURE GetEmployeeBonus (IN empID INT, OUT bonus DECIMAL(10,2))
   AS
   BEGIN
       DECLARE @baseSalary DECIMAL(10,2);
   
       -- Retrieve the base salary
       SELECT @baseSalary = Salary FROM Employees WHERE EmployeeID = empID;
   
       -- Conditional logic to determine the bonus
       IF @baseSalary > 100000
       BEGIN
           SET @bonus = @baseSalary * 0.20; -- 20% bonus
       END
       ELSE
       BEGIN
           SET @bonus = @baseSalary * 0.10; -- 10% bonus
       END
   END;
   

   This procedure calculates an employee's bonus based on their base salary. It uses conditional logic to apply a higher bonus rate for higher salaries

Stored procedures are a powerful feature of relational databases, offering numerous benefits in terms of performance, security, maintainability, and transaction management. By encapsulating complex logic within stored procedures, developers can create efficient, reusable, and secure database operations that enhance the overall robustness of an application.