How to create a MySQL stored procedure with IN parameters?

To make it understand, we are using a table named "student_info" which has the following values ​​-

mysql> Select * from student_info;
+-----+---------+------------+------------+
| id  | Name    | Address    | Subject    |
+-----+---------+------------+------------+
| 101 | YashPal | Amritsar   | History    |
| 105 | Gaurav  | Jaipur     | Literature |
| 110 | Rahul   | Chandigarh | History    |
| 125 | Raman   | Shimla     | Computers  |
+------+--------+------------+------------+
4 rows in set (0.00 sec)

Now with the help of the following query, we will Create a stored procedure with IN parameter that will display all the details of a specific student by providing the name as parameter.

mysql> DELIMITER // ;
mysql> Create PROCEDURE detail(IN S_Name VARCHAR(20))
    -> BEGIN
    -> SELECT * From Student_info WHERE Name = S_Name;
    -> END //
Query OK, 0 rows affected (0.16 sec)
mysql> DELIMITER ;

S_Name is the IN parameter of the stored procedure "detail". If we want to see all details of student name "Gaurav" we can do it with the help of following query -

mysql> CALL detail('Gaurav');
+-----+--------+---------+------------+
| id  | Name   | Address | Subject    |
+-----+--------+---------+------------+
| 105 | Gaurav | Jaipur  | Literature |
+-----+--------+---------+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> CALL detail('Raman');
+-----+-------+---------+-----------+
| id  | Name  | Address | Subject   |
+-----+-------+---------+-----------+
| 125 | Raman | Shimla  | Computers |
+-----+-------+---------+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

The above is the detailed content of How to create a MySQL stored procedure with IN parameters?. For more information, please follow other related articles on the PHP Chinese website!