Passing IN, OUT, and INOUT Parameters in MySQL

Course: MySQL Training Course

Topic: Passing IN, OUT, and INOUT Parameters in MySQL Stored Procedures

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to IN, OUT, and INOUT Parameters

In MySQL, stored procedures can accept values from the calling application and return values after performing database operations. Parameters make stored procedures reusable, flexible, and suitable for different business requirements.

MySQL supports three parameter modes for stored procedures: IN, OUT, and INOUT. Each mode determines how a value is passed into a procedure and whether a changed value can be returned to the caller.

For example, an employee management system may pass an employee ID to a procedure, retrieve the employee's salary, and calculate an updated salary. The parameter mode determines whether the procedure only receives the employee ID, returns a result through an output parameter, or receives and modifies an existing value.

These parameter modes are valid for stored procedures. In MySQL stored functions, parameters are treated as input parameters; functions return their result through the declared return value instead.

Learning Objectives

• Understand what parameters are in MySQL stored procedures.
• Learn the purpose of IN, OUT, and INOUT parameter modes.
• Understand how values move between the caller and a stored procedure.
• Create stored procedures using each parameter mode.
• Execute procedures using the CALL statement.
• Use MySQL session variables to receive OUT and INOUT values.
• Understand the differences between local variables and session variables.
• Identify common errors related to procedure parameters.
• Apply parameter modes to practical database problems.

2. What Is a Parameter in MySQL?

A parameter is a named value declared in a stored procedure's definition. It allows the caller to provide data to the procedure or receive a value from it.

Parameters are declared inside the parentheses of a CREATE PROCEDURE statement. Each parameter has a mode, a name, and a data type.

General Syntax

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

The parameter modes shown above are illustrative. A procedure can have one or more parameters, and each parameter can use the appropriate mode for its purpose.

Example

DELIMITER //

CREATE PROCEDURE GetEmployeeDetails(
    IN p_employee_id INT
)
BEGIN
    SELECT employee_id, employee_name, department
    FROM employees
    WHERE employee_id = p_employee_id;
END //

DELIMITER ;

In this example, p_employee_id is an input parameter. The caller supplies an employee ID, and the procedure uses it to retrieve matching employee details.

3. Understanding the Three Parameter Modes

The easiest way to remember the difference is:

• IN: Input only.
• OUT: Output only.
• INOUT: Input and output.

4. IN Parameters in MySQL

An IN parameter allows the caller to pass a value into a stored procedure. The procedure can use that value in conditions, calculations, and SQL statements.

IN is the default parameter mode in MySQL. If no mode is specified for a procedure parameter, MySQL treats it as an IN parameter.

4.1 Syntax of an IN Parameter

CREATE PROCEDURE procedure_name(
    IN parameter_name data_type
)
BEGIN
    SQL statements;
END;

4.2 Example: Retrieving an Employee by ID

First, create a sample employees table:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(100),
    salary DECIMAL(10,2)
);

Insert some sample records:

INSERT INTO employees
    (employee_id, employee_name, department, salary)
VALUES
    (101, 'Aarav Sharma', 'IT', 45000.00),
    (102, 'Diya Patel', 'HR', 40000.00),
    (103, 'Rohan Mehta', 'Finance', 50000.00);

Create a procedure with an IN parameter:

DELIMITER //

CREATE PROCEDURE GetEmployeeById(
    IN p_employee_id INT
)
BEGIN
    SELECT employee_id, employee_name, department, salary
    FROM employees
    WHERE employee_id = p_employee_id;
END //

DELIMITER ;

4.3 Calling the Procedure

CALL GetEmployeeById(101);

The procedure receives the value 101 through p_employee_id and retrieves the corresponding employee record.

4.4 Expected Result

4.5 Using an IN Parameter in a Calculation

An IN parameter can also be used for calculations.

DELIMITER //

CREATE PROCEDURE CalculateAnnualSalary(
    IN p_monthly_salary DECIMAL(10,2)
)
BEGIN
    SELECT p_monthly_salary * 12 AS annual_salary;
END //

DELIMITER ;

Execute the procedure:

CALL CalculateAnnualSalary(45000.00);

Expected result:

annual_salary
-------------
540000.00

4.6 Important Points About IN Parameters

• The caller supplies the input value when invoking the procedure.
• The parameter can be used in WHERE clauses and calculations.
• The procedure can assign a different value to its local IN parameter, but this change is not returned to the caller through that parameter.
• IN parameters are useful for IDs, dates, names, quantities, prices, and search conditions.
• When a procedure needs to return a value, use an OUT or INOUT parameter, or return an appropriate result set.

5. OUT Parameters in MySQL

An OUT parameter allows a stored procedure to send a value back to the caller. It is useful when the procedure needs to calculate or retrieve a value that the calling application will use afterward.

An OUT parameter starts with a value of NULL inside the procedure. The procedure should assign the required result before returning.

5.1 Syntax of an OUT Parameter

CREATE PROCEDURE procedure_name(
    OUT parameter_name data_type
)
BEGIN
    -- Assign a value to the OUT parameter
END;

5.2 Example: Counting Employees

Suppose a company wants to retrieve the total number of employees in its database. A procedure can calculate the count and return it through an OUT parameter.

DELIMITER //

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

DELIMITER ;

The expression COUNT(*) calculates the number of rows, and SELECT ... INTO stores that result in the OUT parameter.

5.3 Calling a Procedure with an OUT Parameter

To retrieve an OUT parameter value, pass a MySQL session variable in the CALL statement.

CALL CountEmployees(@employee_total);

SELECT @employee_total AS total_employees;

The session variable @employee_total receives the output value after the procedure returns successfully.

For the three sample employees inserted earlier, the result is:

total_employees
---------------
3

5.4 Understanding SELECT ... INTO

Inside a stored procedure, SELECT ... INTO assigns the result of a query to one or more variables.

SELECT COUNT(*)
INTO p_total_employees
FROM employees;

In this statement:

• COUNT(*) calculates the number of employee rows.
• INTO p_total_employees assigns the result to the output parameter.
• The calling application can read the returned value after the procedure completes.

5.5 Example: Returning an Employee's Salary

DELIMITER //

CREATE PROCEDURE GetEmployeeSalary(
    IN p_employee_id INT,
    OUT p_salary DECIMAL(10,2)
)
BEGIN
    SELECT salary
    INTO p_salary
    FROM employees
    WHERE employee_id = p_employee_id;
END //

DELIMITER ;

Call the procedure:

CALL GetEmployeeSalary(101, @employee_salary);

SELECT @employee_salary AS employee_salary;

If employee 101 exists and has a salary of 45000.00, the output variable contains that value.

Important: If a SELECT ... INTO query returns no rows, MySQL raises a NOT FOUND condition and the target variable is not assigned a new value by that query. When the lookup may return no record, initialize the output variable and handle the condition deliberately.

5.6 Handling a Missing Employee

The following example initializes the OUT parameter and uses a NOT FOUND handler to avoid leaving the result undefined when an employee ID does not exist.

DELIMITER //

CREATE PROCEDURE FindEmployeeSalary(
    IN p_employee_id INT,
    OUT p_salary DECIMAL(10,2)
)
BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET p_salary = NULL;

    SET p_salary = NULL;

    SELECT salary
    INTO p_salary
    FROM employees
    WHERE employee_id = p_employee_id;
END //

DELIMITER ;

Execute the procedure:

CALL FindEmployeeSalary(999, @employee_salary);

SELECT @employee_salary AS employee_salary;

If employee 999 does not exist, the output remains NULL. In real applications, a separate status output or a clear error-handling strategy may be useful when NULL could also represent a legitimate value.

5.7 Important Points About OUT Parameters

• OUT parameters are intended to return values to the caller.
• The parameter is initially NULL inside the procedure.
• The procedure must assign the intended output value.
• A session variable is commonly used to receive the result in a SQL client.
• OUT parameters can return counts, totals, calculated values, status codes, or other scalar values.
• A procedure can have multiple OUT parameters.

6. INOUT Parameters in MySQL

An INOUT parameter combines input and output behavior. The caller supplies an initial value, the procedure works with that value, and the procedure can return the modified value to the caller.

INOUT parameters are helpful for calculations that build on an existing value, such as increasing a counter, applying a percentage adjustment, or updating a running total.

6.1 Syntax of an INOUT Parameter

CREATE PROCEDURE procedure_name(
    INOUT parameter_name data_type
)
BEGIN
    -- Read and modify the parameter
END;

6.2 Example: Incrementing a Number

Create a procedure that adds 1 to the value supplied by the caller.

DELIMITER //

CREATE PROCEDURE IncrementValue(
    INOUT p_number INT
)
BEGIN
    SET p_number = p_number + 1;
END //

DELIMITER ;

6.3 Calling the Procedure

Initialize a session variable before passing it to the procedure.

SET @number = 10;

CALL IncrementValue(@number);

SELECT @number AS updated_number;

Expected result:

updated_number
--------------
11

The initial value is 10. The procedure adds 1, and the updated value 11 becomes available to the caller after successful execution.

6.4 Example: Increasing a Salary

Suppose a company wants to calculate an employee's new salary by adding a fixed increase to an existing salary value. An INOUT parameter can receive the original salary and return the adjusted amount.

DELIMITER //

CREATE PROCEDURE IncreaseSalaryAmount(
    INOUT p_salary DECIMAL(10,2),
    IN p_increase DECIMAL(10,2)
)
BEGIN
    SET p_salary = p_salary + p_increase;
END //

DELIMITER ;

Call the procedure:

SET @salary = 45000.00;

CALL IncreaseSalaryAmount(@salary, 5000.00);

SELECT @salary AS updated_salary;

Expected result:

updated_salary
--------------
50000.00

This example changes a value held in a session variable. It does not update the employees table. To persist a salary change in the database, the procedure would need an appropriate UPDATE statement and safeguards.

6.5 Example: Applying a Percentage Increase

DELIMITER //

CREATE PROCEDURE ApplyPercentageIncrease(
    INOUT p_amount DECIMAL(10,2),
    IN p_percentage DECIMAL(5,2)
)
BEGIN
    SET p_amount = p_amount + (p_amount * p_percentage / 100);
END //

DELIMITER ;

Execute the procedure:

SET @amount = 1000.00;

CALL ApplyPercentageIncrease(@amount, 10.00);

SELECT @amount AS amount_after_increase;

Expected result:

amount_after_increase
---------------------
1100.00

The procedure receives 1000.00, calculates a 10 percent increase, and returns 1100.00.

6.6 Important Points About INOUT Parameters

• The caller must initialize the value before calling the procedure.
• The procedure can read the initial value and then modify it.
• The updated value is returned through the parameter after successful execution.
• A session variable is commonly used when calling the procedure directly from a SQL client.
• INOUT parameters are useful for counters, running totals, and value transformations.
• INOUT does not automatically update a database table; the procedure must contain the necessary SQL statements if persistent data changes are required.

7. Understanding MySQL Session Variables

Session variables are user-defined variables that begin with the @ symbol. They are associated with the current database session and can hold values between SQL statements during that session.

They are commonly used to pass and retrieve OUT and INOUT parameter values when calling a stored procedure directly from a MySQL client.

Example

SET @total = 50;

SELECT @total AS current_value;

Expected result:

current_value
-------------
50

A session variable can be passed to a procedure as an OUT or INOUT argument:

CALL CountEmployees(@total);

SELECT @total;

Session Variables vs Local Variables

8. Comparing IN, OUT, and INOUT Parameters

9. Using IN and OUT Together

A stored procedure can use multiple parameter modes. A common pattern is to receive a search value through an IN parameter and return a calculated result through an OUT parameter.

Example: Calculating a Discount

DELIMITER //

CREATE PROCEDURE CalculateDiscountAmount(
    IN p_price DECIMAL(10,2),
    IN p_discount_rate DECIMAL(5,2),
    OUT p_discount_amount DECIMAL(10,2)
)
BEGIN
    SET p_discount_amount =
        p_price * p_discount_rate / 100;
END //

DELIMITER ;

Call the procedure and retrieve the discount:

CALL CalculateDiscountAmount(
    2000.00,
    15.00,
    @discount
);

SELECT @discount AS discount_amount;

Expected result:

discount_amount
---------------
300.00

The first two parameters provide the original price and discount rate. The OUT parameter receives the calculated discount amount.

10. Using INOUT and OUT Together

A procedure may accept an existing value through an INOUT parameter while returning a separate result through an OUT parameter.

Example: Calculating a New Total and Its Increase

DELIMITER //

CREATE PROCEDURE CalculateUpdatedTotal(
    INOUT p_total DECIMAL(10,2),
    IN p_increase DECIMAL(10,2),
    OUT p_difference DECIMAL(10,2)
)
BEGIN
    SET p_total = p_total + p_increase;
    SET p_difference = p_increase;
END //

DELIMITER ;

Call the procedure:

SET @total = 1500.00;

CALL CalculateUpdatedTotal(
    @total,
    250.00,
    @difference
);

SELECT
    @total AS updated_total,
    @difference AS increase_amount;

Expected result:

The INOUT parameter returns the updated total, while the OUT parameter returns the amount added.

11. Passing Parameters to Stored Procedures

MySQL uses the CALL statement to invoke a stored procedure. Arguments must be supplied in the order defined by the procedure's parameter list.

General Syntax

CALL procedure_name(argument1, argument2, argument3);

Example

CALL CalculateDiscountAmount(
    2000.00,
    15.00,
    @discount
);

For OUT and INOUT values, the caller should use an appropriate variable to retrieve the result. When using a MySQL client, a session variable such as @discount is a common choice.

Checking the Returned Values

SELECT @discount;

For multiple output parameters, select each session variable after the procedure call:

SELECT
    @discount AS discount_amount,
    @total AS updated_total,
    @difference AS increase_amount;

12. Complete Practical Example: Employee Salary Calculation

This practical example demonstrates all three parameter modes in one procedure. It accepts an employee ID, receives a salary adjustment percentage, returns the original salary, and returns the new salary through output parameters.

Step 1: Create the Sample Table

CREATE TABLE salary_records (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    salary DECIMAL(10,2) NOT NULL
);

Step 2: Insert Sample Data

INSERT INTO salary_records
    (employee_id, employee_name, salary)
VALUES
    (201, 'Neha Singh', 40000.00),
    (202, 'Kabir Verma', 50000.00),
    (203, 'Anaya Joshi', 60000.00);

Step 3: Create the Stored Procedure

DELIMITER //

CREATE PROCEDURE CalculateEmployeeSalary(
    IN p_employee_id INT,
    INOUT p_increase_rate DECIMAL(5,2),
    OUT p_original_salary DECIMAL(10,2),
    OUT p_new_salary DECIMAL(10,2)
)
BEGIN
    DECLARE v_salary DECIMAL(10,2) DEFAULT NULL;

    SELECT salary
    INTO v_salary
    FROM salary_records
    WHERE employee_id = p_employee_id;

    SET p_original_salary = v_salary;

    SET p_new_salary =
        v_salary + (v_salary * p_increase_rate / 100);

    SET p_increase_rate = p_new_salary - v_salary;
END //

DELIMITER ;

This example illustrates the three modes, but it assumes that the supplied employee ID exists. In production, add explicit handling for a missing employee and validate the increase rate before calculating the new salary.

Step 4: Initialize the INOUT Value

SET @increase_rate = 10.00;

The initial value represents a 10 percent increase.

Step 5: Call the Procedure

CALL CalculateEmployeeSalary(
    201,
    @increase_rate,
    @original_salary,
    @new_salary
);

Step 6: Display the Results

SELECT
    @increase_rate AS increase_amount,
    @original_salary AS original_salary,
    @new_salary AS new_salary;

Expected result for employee 201:

Notice that the INOUT parameter starts as a percentage value of 10.00 and is changed by the procedure to the monetary increase of 4000.00. This is valid as a demonstration, but using one parameter for two different meanings can make code harder to understand. In production, it is usually clearer to keep the percentage as an IN parameter and return the increase amount through a separate OUT parameter.

13. Improved Version: Clear Parameter Responsibilities

The following version keeps each parameter's purpose clear: the employee ID and percentage are inputs, while the original salary, increase amount, and new salary are outputs.

DELIMITER //

CREATE PROCEDURE GetSalaryAdjustment(
    IN p_employee_id INT,
    IN p_increase_rate DECIMAL(5,2),
    OUT p_original_salary DECIMAL(10,2),
    OUT p_increase_amount DECIMAL(10,2),
    OUT p_new_salary DECIMAL(10,2)
)
BEGIN
    DECLARE v_salary DECIMAL(10,2) DEFAULT NULL;

    SELECT salary
    INTO v_salary
    FROM salary_records
    WHERE employee_id = p_employee_id;

    SET p_original_salary = v_salary;
    SET p_increase_amount =
        v_salary * p_increase_rate / 100;
    SET p_new_salary =
        v_salary + p_increase_amount;
END //

DELIMITER ;

Call the procedure:

CALL GetSalaryAdjustment(
    201,
    10.00,
    @original_salary,
    @increase_amount,
    @new_salary
);

SELECT
    @original_salary AS original_salary,
    @increase_amount AS increase_amount,
    @new_salary AS new_salary;

This version is easier to understand because each parameter has a consistent meaning. As with the previous example, add appropriate error handling for missing employee records and invalid input values before using this pattern in production.

14. Common Errors When Passing Parameters

15. Best Practices for Using IN, OUT, and INOUT

• Use IN parameters for values the procedure needs to receive.
• Use OUT parameters for values that the procedure needs to return.
• Use INOUT parameters only when the initial value and modified value represent a clear, related concept.
• Choose meaningful parameter names such as p_employee_id and p_total_amount.
• Use suitable data types and numeric precision.
• Validate input values before using them in calculations or data modifications.
• Initialize INOUT session variables before calling a procedure.
• Check OUT variables after the CALL statement.
• Handle missing records and SQL exceptions deliberately.
• Keep each parameter's responsibility clear and consistent.
• Avoid using an OUT parameter without assigning a result on every expected execution path.
• Test procedures with normal values, boundary values, invalid values, and missing records.
• Do not assume a procedure has updated a database table merely because it returned a modified parameter.
• Use clear documentation to explain parameter meaning, units, valid ranges, and return behavior.

16. Practice Exercises

1. Create a procedure that accepts a product ID through an IN parameter and displays the product details.
2. Create a procedure that accepts an employee ID and returns the employee's salary through an OUT parameter.
3. Create a procedure that accepts a price and tax rate and returns the tax amount through an OUT parameter.
4. Create a procedure that accepts an initial counter through an INOUT parameter and increases it by 5.
5. Create a procedure that accepts a quantity and unit price and returns the total cost.
6. Create a procedure that accepts an order amount and returns the discount and final amount through two OUT parameters.
7. Create a procedure that accepts a percentage through an IN parameter and returns a calculated bonus.
8. Demonstrate the difference between a local variable and a session variable.
9. Call a procedure with an OUT parameter and display the returned value using SELECT.
10. Call a procedure with an INOUT parameter twice and observe how the value changes.
11. Modify a procedure to handle a missing employee ID.
12. Explain why an IN parameter's changed local value is not returned to the caller through that parameter.

17. Interview Questions and Answers

Q1. What are parameters in MySQL stored procedures?

Parameters are named values declared in a procedure definition. They allow a procedure to receive input, return output, or do both.

Q2. What is an IN parameter?

An IN parameter passes a value into a stored procedure. The procedure can use or locally modify the value, but a local modification is not returned through that parameter.

Q3. What is an OUT parameter?

An OUT parameter allows a procedure to return a value to the caller. It starts as NULL inside the procedure and should be assigned the intended output.

Q4. What is an INOUT parameter?

An INOUT parameter receives an initial value, allows the procedure to modify it, and returns the changed value to the caller.

Q5. Which parameter mode is the default in MySQL?

IN is the default mode for stored procedure parameters when no mode is specified.

Q6. Can a stored procedure have multiple OUT parameters?

Yes. A procedure can declare multiple OUT parameters to return several values.

Q7. How do you retrieve an OUT parameter in MySQL?

When calling a procedure from a SQL client, pass a session variable such as @total and read it afterward using SELECT.

Q8. Does an OUT parameter need an initial value from the caller?

No. Inside the procedure, an OUT parameter initially has a value of NULL. The procedure should assign the intended output value.

Q9. Does an INOUT parameter need to be initialized?

Yes. The caller should initialize the value before passing it to the procedure.

Q10. What is the difference between IN and INOUT?

IN provides input whose local changes are not returned through the parameter. INOUT provides input and returns the parameter's modified value.

Q11. Can OUT and INOUT parameters be used in stored functions?

No. MySQL stored function parameters are treated as input parameters. OUT and INOUT parameter modes are supported for stored procedures.

Q12. What does SELECT ... INTO do?

It assigns the result of a SELECT query to one or more variables, such as an OUT parameter.

Q13. Why might an OUT parameter remain NULL?

The procedure may not assign it, a query may return no row, or an error may prevent the expected assignment.

Q14. Do INOUT parameters automatically update table records?

No. They return a changed parameter value. An UPDATE statement is needed if the procedure must persist a change to a table.

Q15. What is the CALL statement used for?

CALL invokes a stored procedure and supplies the arguments defined in the procedure's parameter list.

Q16. What is a session variable?

A session variable is a user-defined variable, usually written with an @ prefix, that is available during the current database session.

Q17. What is an example of an IN parameter?

A customer ID passed to a procedure that retrieves the customer's details is a typical IN parameter.

Q18. What is an example of an OUT parameter?

A procedure that calculates the number of employees and returns the count through an OUT parameter is a typical example.

Q19. What is an example of an INOUT parameter?

A procedure that receives a counter value, increments it, and returns the new counter value is a typical example.

Q20. What should you consider when designing parameter modes?

Choose the mode according to the direction of data flow, define clear parameter meanings, validate inputs, handle missing records, and test output behavior.

18. Quick Revision

• IN passes a value into a stored procedure.
• OUT returns a value from a stored procedure.
• INOUT passes an initial value and returns a modified value.
• IN is the default stored procedure parameter mode.
• OUT parameters start as NULL inside the procedure.
• INOUT parameters should be initialized before the CALL statement.
• CALL is used to execute a stored procedure.
• Session variables such as @total are commonly used to retrieve OUT and INOUT values in SQL clients.
• SELECT ... INTO assigns query results to variables.
• Stored functions do not support OUT or INOUT parameter modes.
• Changing an IN parameter inside a procedure does not return the changed value through that parameter.
• Parameter values do not automatically persist to database tables.
• Validate values and handle missing rows before using routines in production.

19. Conclusion

Passing IN, OUT, and INOUT parameters is a fundamental skill for working with MySQL stored procedures. IN parameters provide values for processing, OUT parameters return calculated or retrieved results, and INOUT parameters allow a procedure to receive and modify an existing value.

Understanding these modes makes it easier to build reusable procedures for employee management, order processing, discount calculations, reporting, and other database tasks. Good parameter design also requires appropriate data types, input validation, error handling, and clear responsibilities for each value.

For additional learning, visit the JustAcademy MySQL Training Course or register for a course demo.

20. Additional Learning Resources

• MySQL Official Documentation: CREATE PROCEDURE and CREATE FUNCTION
• MySQL Official Documentation: CALL Statement
• MySQL Official Documentation: Stored Routine Syntax
• JustAcademy MySQL Training Course
• JustAcademy Course Demo Registration