Parameters are arguments that are supplied to a stored procedure while calling or executing it. A stored procedure can accept any number of parameters and they are enclosed within parenthesis after the name of stored procedure.
Syntax of specifying a parameter in stored procedure is
where,
parameter type is the type of parameter. Possible values of parameter type are IN, OUT or INOUT. All these are discussed later in this section.
parameter name is a user defined name, and
data type is the type of value that this parameter will contain. It should be from among valid MySQL data types such as INT, VARCHAR, DECIMAL, DOUBLE etc.
Valid examples of parameters are:
IN name VARCHAR(50)
OUT sum INT
INOUT counter INT
Parameter types
IN : It represents that the parameter is an input parameter. Its value can only be read inside the stored procedure but cannot be modified.
Value of IN parameters are supplied by the caller while calling the procedure and they make the procedure executed for different values.
If the stored procedure changes the value for this parameter, it will not be updated for the caller. This also means that the stored procedure works on the copy of the input parameter.
OUT : Represents the output parameter which is returned by the procedure to the caller. A stored procedure cannot read a value from this parameter.
If the caller tries to send a value into OUT parameter, even then, the stored procedure will not be able to read and utilize it.
INOUT : A parameter of this type can be used as an input and output parameter. This means that a stored procedure may read its value, update it and send it back to the caller.
Caller will see the value updated by the stored procedure.
IN parameter example
An example of a stored procedure using an IN parameter is given below.
DELIMITER %%
CREATE PROCEDURE getstudentdata(IN studentid INT)
BEGIN
SELECT name FROM student WHERE id=studentid;
END %%
DELIMITER ;
This procedure accepts studentid as IN parameter and fetches the record for the supplied id. It will be invoked using below statement.
CALL getstudentdata(1);
When this procedure is called using above statement, then below is the output. This is the name of student with id 1 which is supplied as the input parameter while calling the procedure.
Notice that the data type of input parameter and the type of value supplied is the same.
OUT parameter example
Following is an example of a stored procedure using OUT parameter.
DELIMITER %%
CREATE PROCEDURE getstudentdata(OUT studentname VARCHAR(45))
BEGIN
SELECT name INTO studentname FROM student WHERE id=4;
END %%
DELIMITER ;
Above procedure fetches the name of student with id 4 and writes it into the output parameter.
This procedure is executed using below syntax.
CALL getstudentdata(@studentname);
SELECT @studentname;
Following points should be noted about a stored procedure with output parameters.
- While calling the procedure, variable name is declared using
@character and SELECT command is used to get the value of the variable. - INTO keyword is used to write the value of a column into the output parameter.
- It is not necessary that names of parameter used in the procedure and that used while calling the procedure are same.
- Data type of output variable in stored procedure definition should be the same as the value being written into it.
Output will be
which is the name of student with id 4 returned by the stored procedure.
You can also use also use an alias with SELECT to change the header of output as
SELECT @studentname AS ‘Student Name’;
You can use multiple output parameters in the same procedure as shown below.
DELIMITER %%
CREATE PROCEDURE getstudentdata(OUT studentname VARCHAR(45), OUT studentname INT)
BEGIN
SELECT name, age INTO studentname, studentage FROM student WHERE id=4;
END %%
DELIMITER ;
INOUT parameter example
This parameter serves the purpose of both IN and OUT parameter. This means that the procedure can both read from it and write to it.
Thus, if you want to supply a value to the stored procedure and return a value from the stored procedure and the types of both the values are the same, then use this type of parameter. Example,
DELIMITER %%
CREATE PROCEDURE getstudentdata(INOUT sidage INT)
BEGIN
SELECT age INTO sidage FROM student WHERE id=sidage;
END %%
DELIMITER ;
Above procedure fetches the age of a student with a given id.
It receives an INOUT parameter. This parameter is used in WHERE condition of the SQL query and the same parameter is used to hold the result.
Thus, when the procedure is called, INOUT parameter holds the id of student and when the procedure completes, this parameter holds the age of student. Use the below statements to call it.
SET @sidage=2;
CALL getstudentdata(@sidage);
SELECT @sidage AS ‘Student Age’;
Output when above procedure is called is as shown below.
It shows the age of student whose id is 2. This id is supplied as a parameter while invoking the procedure. Also, the result(or age) is written in the same parameter. This is because the type of parameter is INOUT.
