Stored Procedures Overview

Stored procedures are precompiled SQL statements stored within the database. They offer several advantages, including:

• Improved performance: Less network traffic as the code is executed on the server.
• Code reusability: Procedures can be called multiple times from different applications or queries.
• Security: Access control can be managed at the procedure level, restricting direct table access.
• Data consistency: Procedures can enforce business rules and data integrity.

Parameters in Stored Procedures

Parameters allow you to pass data into and receive data from stored procedures. MySQL supports three types of parameters:

• IN: Input parameters - Used to pass values into the procedure. The procedure can read the value but cannot modify it outside the procedure's scope.
• OUT: Output parameters - Used to pass values out of the procedure. The procedure assigns a value to the parameter, which can then be retrieved by the caller. The initial value of an OUT parameter passed into the stored procedure is ignored, and it is reset when the stored procedure begins its execution.
• INOUT: Input/Output parameters - Used to pass a value into the procedure, and the procedure can modify the value and pass it back out to the caller.

Declaring Parameters

When creating a stored procedure, you define parameters using the following syntax:

 CREATE PROCEDURE procedure_name (
    IN  parameter_name1 data_type,
    OUT parameter_name2 data_type,
    INOUT parameter_name3 data_type
)
BEGIN
    -- SQL statements
END; 

Example: IN Parameter

This example demonstrates a procedure that takes an employee ID as input and returns the employee's name.

 DELIMITER //

CREATE PROCEDURE GetEmployeeName (
    IN employee_id INT
)
BEGIN
    SELECT first_name, last_name
    FROM employees
    WHERE employee_id = employee_id;
END //

DELIMITER ;

-- Calling the procedure
CALL GetEmployeeName(123); 

Example: OUT Parameter

This example demonstrates a procedure that retrieves the total number of employees and stores it in an output parameter.

 DELIMITER //

CREATE PROCEDURE GetTotalEmployees (
    OUT total_employees INT
)
BEGIN
    SELECT COUNT(*) INTO total_employees
    FROM employees;
END //

DELIMITER ;

-- Calling the procedure
CALL GetTotalEmployees(@total);
SELECT @total; 

Example: INOUT Parameter

This example demonstrates a procedure that increments a given number by 5 and returns the updated value through the INOUT parameter.

 DELIMITER //

CREATE PROCEDURE IncrementByFive (
    INOUT num INT
)
BEGIN
    SET num = num + 5;
END //

DELIMITER ;

-- Calling the procedure
SET @number = 10;
CALL IncrementByFive(@number);
SELECT @number; 

Variables in Stored Procedures

Variables are used to store temporary values within the procedure's scope. They can be used to hold intermediate results, loop counters, or any other data required during the procedure's execution.

Declaring Variables

Variables are declared within the BEGIN...END block of the procedure using the DECLARE statement. Variables must be declared *before* they are used.

 DECLARE variable_name data_type [DEFAULT initial_value]; 

Using Variables

Values are assigned to variables using the SET statement.

 SET variable_name = value; 

Example: Using Variables

This example demonstrates a procedure that calculates the average salary of employees and stores it in a variable.

 DELIMITER //

CREATE PROCEDURE CalculateAverageSalary ()
BEGIN
    DECLARE total_salary DECIMAL(10, 2);
    DECLARE employee_count INT;
    DECLARE average_salary DECIMAL(10, 2);

    SELECT SUM(salary) INTO total_salary FROM employees;
    SELECT COUNT(*) INTO employee_count FROM employees;

    SET average_salary = total_salary / employee_count;

    SELECT average_salary; -- Or use an OUT parameter to return the result
END //

DELIMITER ;

-- Calling the procedure
CALL CalculateAverageSalary(); 

Best Practices

• Use descriptive names: For both parameters and variables to improve code readability.
• Validate input parameters: To prevent unexpected errors or security vulnerabilities.
• Handle errors gracefully: Implement error handling mechanisms to manage potential exceptions. Consider using DECLARE CONTINUE HANDLER FOR SQLEXCEPTION or similar mechanisms.
• Comment your code: To explain the purpose of each section and make it easier to understand.
• Keep procedures concise: If a procedure becomes too complex, consider breaking it down into smaller, more manageable procedures.