Stored Procedure Parameters

You should be already familiar with stored procedures in SQL. Now it's time to dive a little deeper into them. Often we have situations when we need to pass some data into the procedure and then work with it. In this case, parameters will help us. In this topic, we will consider in detail using parameters in stored procedures. And then you will have a chance to practice them!

Types of parameters

There are three types of parameters in stored procedures: IN, OUT and INOUT. Let's discuss them more closely.

The IN mode means that some argument will be passed to the stored procedure, but it is not going to be changed while the procedure is processed. Even if you modify the value of the IN-parameter inside the procedure, the original value will remain when the procedure is finished. In other words, the stored procedure copies inserted parameters and works with them throughout the process.

The OUT parameter works differently. It can be changed during the procedure's work, and its new value is passed back after the finish of the program. The OUT parameter records the value into the session variable that can be accessed after the stored procedure is executed. An OUT parameter is used to return values from a procedure or function back to the calling program.

The INOUT parameter is a combination of IN and OUT types, as you may have guessed. It means that the argument will be passed into the procedure, which can change the value during the execution, and then return the modified value to the session variable.

Parameter syntax

Now let's take a look at the basic syntax of defining a parameter in stored procedures:

[IN | OUT| INOUT] parameter_name datatype

• First, you need to specify the parameter type: IN, OUT, or INOUT.

• Then specify the name of the parameter. It can be any name you want, but it rather be meaningful.

• Third, specify the datatype of the parameter.

Now we will review an example of using the parameter in the stored procedure:

CREATE PROCEDURE SomeName(
  IN SomeParameter VARCHAR(50),
  OUT SomeParameter1 INT,
  -- You can add here as many parameters as you want
)
BEGIN
  -- PROCEDURE BODY;
END;

It is pretty simple, but it shows the basic syntax. In the next paragraph, we will look at putting parameters to practice.

Using the parameters

First of all, we will discuss using the IN parameter. Let's imagine we have a table called customers. This table has the city field that shows where customers are from.

We will create a stored procedure to select customers by specified city.

CREATE PROCEDURE GetCustomersByCity(
  IN input_city VARCHAR(255)
)
BEGIN
  SELECT *
  FROM customers
  WHERE city = input_city;
END;

So, let's call our procedure to find all the customers from Washington:

CALL GetCustomersByCity("Washington");

The result of the procedure will be the following:

All the people from Washington, precisely what we want!

To introduce an OUT parameter, let's do some math. We will create a procedure that will get a number, divide it by 2, and find the remainder of the division. There will be two parameters: the IN parameter input_number and the OUT parameter out_number.

CREATE PROCEDURE GetRemainder(
  IN input_number INT,
  OUT out_number INT
)
BEGIN
  SELECT input_number % 2
  INTO out_number;
END;

So, first of all, we need to call the procedure specifying the session variable. We will call it remainder. Then add the variable. For example, let's find the remainder of 35 divided by 2:

CALL GetRemainder(35, @remainder);
SELECT @remainder;

We will get this as a result:

And that's correct, the remainder of 35 divided by two is 1.

Now we should have a look at the INOUT parameter type. The parameter of this type can be changed during the procedure's execution and takes the new value. A simple example of using the INOUT parameter is to increase a value. We can change the value that will be turning into another value by calling the procedure repeatedly. For this example, we will create a session variable called a and will repeatedly increase it by one in our procedure:

SET @a = 0;

CREATE PROCEDURE Inc(
  INOUT a INT
)
BEGIN
  SET a = a + 1;
END;

Now let's call this procedure a couple of times and see how our a variable will change:

CALL Inc(@a); -- 1
CALL Inc(@a); -- 2
CALL Inc(@a); -- 3
CALL Inc(@a); -- 4

Then if we select this variable after four times execution, we will get 4 as the result. That happened because 1 was added to the a variable four times.

SELECT @a;
-- Output: 4

Conclusion

Amazing! You have learned about parameters in stored procedures in MySQL. Now you know how to use them, and we are sure they will be helpful in your projects. We will repeat some key facts then you can begin to practice:

• There are three types of parameters: IN, OUT and INOUT.

• You can use as many parameters as you want in your stored procedure.

• The IN parameter doesn't change its value during the procedure work, but OUT and INOUT do.