Parameter is the variable which is passed to the stored procedure. MYSQL can have 4 types of parameters passed to the stored procedure.

Empty parameter: Here no parameter is passed to the stored procedure.

Syntax:

CREATE PROCEDURE display();

CALL display();

IN: Here the parameter is input. This is default for the stored procedure where the parameter is sent to the procedure for modification. However the modification of the value has effect only within the stored procedure. With the END statement, the value of the parameter is back to the original value sent into the procedure.  

Example:

DELIMITER //

CREATE PROCEDURE display(IN text VARCHAR(10))

BEGIN

SELECT * FROM tblGreetings WHERE greeting = text;

END//

DELIMITER ;

Here 'text' is the IN parameter for the stored procedure. It selects the greeting from tblGreetings where value is equal to text. Suppose we need to select the text='hello' call the procedure as:

CALL  display('hello');

OUT: Here the parameter is output. The value of OUT parameter can be changed inside the stored procedure and it is reflected back in the calling program. The initial value of OUT is changed after the END statement and cannot be accessed back in calling program.

Example:

DELIMITER //

CREATE PROCEDURE display(OUT number INT)

BEGIN

SELECT count(*) INTO number FROM tblItems WHERE itemCount > 5;

END//

DELIMITER ;

 

CALL display(@number);

This method returns to the OUT variable the count of items whose itemCount is more than 5.

INOUT: This parameter is both input and output. It means that calling program can pass the parameter, modify the parameter in the stored procedure and get back the changed value.

Example:

DELIMITER //

CREATE PROCEDURE display(INOUT number INT)

BEGIN

SELECT count(*) INTO number FROM tblItems WHERE itemCount > 5;

END//

DELIMITER ;

 

CALL display(@number);

The number is INOUT parameter whose value can be used inside stored procedure and in calling function.