Parameters in the MySQL Stored Procedures.

# In this tutorial you will about the parameters in the MySQL Stored Procedures.

1. With the parameters you can make the stored procedure more useful & reuseable.

2. There are 3 mods of parameters

IN, OUT, INOUT

I will explain these 3 mods in detail in this video so be with me.

1. IN Parameters.

1. IN is the default mode.

2. When you you define IN parameter in the stored procedure, while calling the SP you have to pass the parameter else you will get an error. like this "Error Code: 1318. Incorrect number of arguments for PROCEDURE classicmodels.GetOfficeByCountry; expected 1, got 0"

3. IN parameter is protected. it means even when you change the value of IN parameter inside the SP, its og value is unchanged after SP ends.

2. OUT parameters.

1. VALUE of the OUT parameter can be changed in the SP. & its new value will be returned to where it has been called.

2. Stored Procedure cannot access the initial value of OUT parameter when it starts.

3. OUT is basically same as return statement as JS. look at the following example.

```

DELIMITER $$

CREATE PROCEDURE GetOrderCountByStatus (

IN orderStatus VARCHAR(25),

OUT total INT

)

BEGIN

SELECT COUNT(orderNumber)

INTO total

FROM orders

WHERE status = orderStatus;

END$$

DELIMITER ;

```

4. if you see above example we are using OUT parameter & we are setting count of the "total" orders based on the status passed & then returning total to the location where the SP is called.

You can call above stored procedure like this.

CALL GetOrderCountByStatus('in process',@total);

SELECT @total AS total_in_process;

3. INOUT Parameter.

```

DELIMITER $$

CREATE PROCEDURE SetCounter(

INOUT counter INT,

IN inc INT

)

BEGIN

SET counter = counter + inc;

END$$

DELIMITER ;

```

If you see above example we are accepting 2 parameters 1st if of INOUT mode & 2nd is of IN mode. in order to get any value back from the sp you have to use the OUT or INOUT parameter.

# IMP TO Note.

1. You cannot hold returned VALUE of SP like this.

SET @totalCountOfUsers = 0;

SET @totalCountOfUsers = CALL SP_GET_ALL_USERS();

assigning like this is not allowed in Stored procedures. that's why you have to use OUT OR INOUT mode.

2. IN the SP you cannot assign VALUE to incoming parameters of any mode.

CREATE PROCEDURE `SP_GET_ALL_USERS`(INOUT total INT)

BEGIN

total = 5;

END

Error: This will give you an error of Invalid syntax. please check your mysql syntax.

3. Now you will get question like how i can assign the new value to incoming parameter. you should use "SELECT INTO" syntax to assign value to incoming params.

Query:- "SELECT COUNT(orderNumber) INTO total FROM orders";

This is equal to "total = :anyNumber"

Read More