Parameters in Stored Routines

While executing any stored routine sometimes we need to pass on certain values in stored procedures these values are handled as Parameters. Their declaration consists of three parts :

• Type of parameter
• Parameter name
• Data-type of the parameter

Type of Parameter

There are three types of parameters :

• IN - This is by default so in case type is not declared its taken as in. As the name is indicative IN type parameters only accept values
• OUT - These type of variables can be used if the values need to be passed back to calling block/statement
• INOUT - This can pass the vale to the stored routine and take back an updated value back to calling block/statement

Parameter Name

Parameter name follows the same naming conventions as followed while creating database objects.

Data-type of the parameter

All the data-types that are allowed to create tables can be used to define the parameters in stored routines

Lets take an example to understand the parameters better.

We want to know by how much amount the salary will be incremented for a particular employee.

DELIMITER $$

CREATE  PROCEDURE `salary_increment_expected`(in increment_percent decimal(5,2), in employee_id int(11),out increment decimal(10,2))
Begin
select salary*increment_percent/100 into increment from employee_master where id=employee_id;
end$$

DELIMITER ;

The above code creates a stored procedures with three parameters two with type in and one with type as out While calling this stored procedure the first two will have values passed and third will have the parameter passed which will get the value OUT from the stored procedure.

To test execute :

call salary_increment_expected(5,1,@increment);

select @increment;

Output will be something similar as shown in attached image.

Lets see an example of INOUT. We will create a stored procedure to check what is the expected new salary after an increment.

DELIMITER $$
CREATE PROCEDURE `salary_expected`(in increment_percent decimal(5,2), inout salary decimal(10,2))
Begin
select salary*(1+increment_percent/100) into salary;
end$$
DELIMITER ;

The above code creates a stored procedures with two parameters one with type in and one with type as inout . While calling this stored procedure both parameters will have values passed but the second one will get the new value OUT as well.

To test the same execute the following

select 5000 into @salary;
call salary_expected(5,@salary);
select @salary;

The result will be something as shown in the below image

Previous
Manage stored routines