Stored Procedure

Stored Procedure — a set of SQL statements that can be stored and executed on a database server.

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.

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;

• Stored procedures can accept input parameters (IN), output parameters (OUT), and input-output parameters (INOUT).
• 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.
• 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.
• Stored procedures can contain conditional statements (IF...ELSE, CASE) and loops (WHILE, LOOP) to perform more complex logic and iterative operations.