MySQL Stored Procedure Parameters

In MySQL, stored procedures are subroutines that are stored in the database and can be called from various parts of your application. Stored procedures can have parameters, which are variables that are passed to the procedure when it is called. These parameters can be used to provide input to the procedure or to return output from the procedure.

MySQL stored procedures can have the following types of parameters:

IN parameters
OUT parameters
INOUT parameters

IN parameters

In MySQL stored procedures, IN parameters are used to pass values into the procedure from the calling code. An IN parameter is a variable that is used to hold a value that will be used within the procedure. It is declared using the IN keyword.

Here’s an example of a stored procedure that takes two IN parameters:

CREATE PROCEDURE get_employee_salary(IN emp_id INT, IN emp_name VARCHAR(50))
BEGIN
  SELECT salary FROM employee WHERE id = emp_id AND name = emp_name;
END;

In this example, the procedure get_employee_salary takes two IN parameters – emp_id of type INT and emp_name of type VARCHAR(50). These parameters are used in the WHERE clause of the SELECT statement to filter the result set based on the values passed in.

When calling the procedure, you would pass in the values for the IN parameters like this:

CALL get_employee_salary(123, 'John Doe');

In this example, we’re passing in the values 123 and ‘John Doe’ for the emp_id and emp_name parameters, respectively.

IN parameters are useful when you need to pass values into a procedure that will be used in the processing of the procedure. They allow you to write more flexible and reusable code, as you can pass in different values for the parameters each time the procedure is called.

OUT parameters

In MySQL stored procedures, OUT parameters are used to return values from the procedure back to the calling code. An OUT parameter is a variable that is declared using the OUT keyword in the parameter list of the procedure.

Here’s an example of a stored procedure that takes one IN parameter and one OUT parameter:

CREATE PROCEDURE get_employee_salary(IN emp_id INT, OUT salary INT)
BEGIN
  SELECT salary INTO salary FROM employee WHERE id = emp_id;
END;

In this example, the procedure get_employee_salary takes one IN parameter – emp_id of type INT, and one OUT parameter – salary of type INT. The SELECT statement in the procedure retrieves the salary of the employee with the specified emp_id, and assigns it to the salary OUT parameter using the INTO keyword.

When calling the procedure, you would pass in the value for the IN parameter like this:

CALL get_employee_salary(123, @emp_salary);

In this example, we’re passing in the value 123 for the emp_id parameter, and using a user-defined variable @emp_salary as the OUT parameter. After the procedure is executed, the value of @emp_salary will be set to the salary of the employee with the specified emp_id.

OUT parameters are useful when you need to return values from a procedure back to the calling code. They allow you to write more flexible and reusable code, as you can retrieve different values for the OUT parameters each time the procedure is called.

INOUT parameters

In MySQL stored procedures, INOUT parameters are used to pass values into the procedure and also return values from the procedure back to the calling code. An INOUT parameter is a variable that is declared using the INOUT keyword in the parameter list of the procedure.

Here’s an example of a stored procedure that takes one INOUT parameter:

CREATE PROCEDURE add_bonus(INOUT salary INT)
BEGIN
  SET salary = salary + 500;
END;

In this example, the procedure add_bonus takes one INOUT parameter – salary of type INT. The procedure adds 500 to the value of salary and assigns the result back to the same parameter using the SET statement.

When calling the procedure, you would pass in the value for the INOUT parameter like this:

SET @emp_salary = 5000;
CALL add_bonus(@emp_salary);
SELECT @emp_salary;

In this example, we’re setting the value of @emp_salary to 5000, and passing it into the procedure as the INOUT parameter. After the procedure is executed, the value of @emp_salary will be updated to 5500, which is the result of adding 500 to the original value of 5000.

INOUT parameters are useful when you need to pass values into a procedure and also return updated values back to the calling code. They allow you to write more flexible and reusable code, as you can modify the values of the INOUT parameters within the procedure and retrieve the updated values in the calling code.

Share on Facebook

Save