Stored Procedures : Stored Procedures are the functions which sits on the database side and provide the services to the user by calling it. Stored Procedures are same as we like functions in our code. Stored Procedures can be created with having no parameters and can be one or more parameters. In this article I will show you what are the parameter we can send or how we can send those arguments to the Stored Procedure. We can process those parameters inside the Stored Procedure and send back the results. So before starting to write the Stored Procedure, you need to know the modes of the parameters, you can thinks modes as a type of parameters we can send to Stored Procedures. There are three modes of parameter IN, OUT and INOUT.

• IN : This is the default mode. If we have defined the IN parameter in the Stored Procedure we have to pass it's value from the calling function. The Stored Procedure creates the copy of this variable and work on it, the original value of the variable doesn't change.

• OUT : This parameter is used to return the result from the procedure.

• INOUT : This parameter can be used as IN and OUT parameter. It is the combination of both. We can pass the parameter value and get back result using the same variable.

Syntax :

MODE paramName paramType(size);

Here

• MODE specifies the IN, OUT or INOUT depends on needs.
• paramName is the parameter name.
• paramType is the data type of parameter (e.g. int, varchar etc.)
• size is the size of parameter.

Stored Procedure could have more than one parameters and each parameter should be separate with comma(',').

Example of Stored Procedure using IN and OUT parameter. You can have any of them or both as per your needs.

The following procedure is return the square of the number passed as an argument.

DELIMITER $$
CREATE PROCEDURE GetSquare(IN num int default 0, OUT result int default 0)
    BEGIN
        SET result =  num *  num ;
    END $$

Now we call the function GetSquare :

Call GetSquare(25, @result);
select @result;

Output : 625