What are the different modes of parameters used by MySQL stored procedure?

MySQLMySQLi Database

Parameters make the stored procedure more useful and flexible. In MySQL, we have the following three kinds of modes −

IN mode

It is the default mode. When we define an IN parameter in a stored procedure, the calling program has to pass an argument to the stored procedure. The value of an IN parameter is protected which means that even the value of the IN parameter is changed inside the stored procedure; its original value is retained after the stored procedure ends.

OUT mode

The value of an OUT parameter can be changed inside the stored procedure and its new value is passed back to the calling program. It is to be noticed that the stored procedure cannot access the initial value of the OUT parameter when it starts.

INOUT mode

An INOUT parameter is the combination of IN and OUT parameters which means that the calling program may pass the argument, and the stored procedure can modify the INOUT parameter and pass the new value back to the calling program.

Syntax for defining a parameter

Following is the syntax of defining a parameter in the stored procedure −

MODE parameter_name parameter_type(parameter_size)

Here, MODE can be IN, OUT or INOUT which depends upon the purpose of the parameter in the stored purpose.

  • Parameter_name is the name of the parameter.
  • Parameter_type is the data type of parameter.
  • Parameter_size is the size of the parameter
Published on 21-Feb-2018 12:21:50