MySQL Stored Procedure Parameters

MySQL Stored Procedure Parameters

In MySQL, stored procedures can accept parameters that allow them to perform dynamic operations based on the input provided. These parameters can be defined in three modes: IN, OUT, and INOUT. Each mode serves a specific purpose, enabling the stored procedure to either receive input, provide output, or both.

Parameter Modes

1. IN Parameter:

   • Passes a value to the stored procedure.
   • This is the default parameter type.
   • The value is read-only and cannot be modified inside the procedure.

2. OUT Parameter:

   • Used to return a value from the stored procedure.
   • The parameter is write-only and cannot hold an initial value.

3. INOUT Parameter:

   • Combines the features of IN and OUT.
   • The parameter accepts a value, allows modification inside the procedure, and returns the modified value.

Syntax

CREATE PROCEDURE procedure_name(
    IN param1 datatype,
    OUT param2 datatype,
    INOUT param3 datatype
)
BEGIN
    -- Procedure logic here
END;

Examples

1. Using IN Parameters

A procedure that takes input and performs an operation.

DELIMITER $$

CREATE PROCEDURE get_employee_salary(
    IN emp_id INT
)
BEGIN
    SELECT salary
    FROM employees
    WHERE employee_id = emp_id;
END$$

DELIMITER ;

Call the procedure:

CALL get_employee_salary(101);

2. Using OUT Parameters

A procedure that calculates and returns a value.

DELIMITER $$

CREATE PROCEDURE calculate_bonus(
    IN emp_id INT,
    OUT bonus_amount DECIMAL(10,2)
)
BEGIN
    SELECT salary * 0.10 INTO bonus_amount
    FROM employees
    WHERE employee_id = emp_id;
END$$

DELIMITER ;

Call the procedure:

CALL calculate_bonus(101, @bonus);
SELECT @bonus AS BonusAmount;

Result:

BonusAmount
500.00

3. Using INOUT Parameters

A procedure that modifies an input value and returns it.

DELIMITER $$

CREATE PROCEDURE update_salary(
    INOUT emp_salary DECIMAL(10,2)
)
BEGIN
    SET emp_salary = emp_salary * 1.10; -- Increase salary by 10%
END$$

DELIMITER ;

Call the procedure:

SET @salary = 5000.00;
CALL update_salary(@salary);
SELECT @salary AS UpdatedSalary;

Result:

UpdatedSalary
5500.00

4. Combining IN, OUT, and INOUT Parameters

A procedure that demonstrates all three parameter modes.

DELIMITER $$

CREATE PROCEDURE employee_performance(
    IN emp_id INT,
    OUT performance_score INT,
    INOUT feedback TEXT
)
BEGIN
    SELECT score INTO performance_score
    FROM performance
    WHERE employee_id = emp_id;

    SET feedback = CONCAT(feedback, ' - Evaluation Completed');
END$$

DELIMITER ;

Call the procedure:

SET @feedback = 'Initial Feedback';
CALL employee_performance(101, @score, @feedback);
SELECT @score AS Score, @feedback AS Feedback;

Result:

Score	Feedback
85	Initial Feedback - Evaluation Completed

Key Points

1. Parameter Scope:

   • Parameters can be used like variables within the procedure body.

2. Default Values:

   • Parameters do not support default values. You must provide them during the procedure call.

3. Restrictions:

   • Parameter names must be unique and cannot match the names of variables, columns, or other identifiers used in the procedure.

4. Handling NULL Values:

   • When working with OUT or INOUT parameters, ensure the values are not NULL unless explicitly intended.

5. Using Variables with Parameters:

   • Use user-defined variables (@var) to store and retrieve values from OUT or INOUT parameters.

Benefits of Using Parameters

1. Reusability:
   • Procedures with parameters can handle a wide range of inputs, making them more versatile.
2. Efficiency:
   • Parameters reduce hardcoding, making procedures more adaptable to changing requirements.
3. Dynamic Behavior:
   • Combine parameters with conditional logic for customized execution paths.

Practical Use Cases

• Fetching data dynamically based on IN parameters.
• Returning computed results using OUT parameters.
• Passing and modifying values via INOUT parameters for complex operations.

Let me know if you'd like further examples or additional details!