Use IN() to get only a particular record in a MySQL stored procedure?

Let us first create a table −

mysql> create table DemoTable2004
(
   UserId varchar(20),
   UserName varchar(20)
);
Query OK, 0 rows affected (0.57 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable2004 values('John_123','John');
Query OK, 1 row affected (0.93 sec)
mysql> insert into DemoTable2004 values('23456_Carol','Carol');
Query OK, 1 row affected (0.25 sec)
mysql> insert into DemoTable2004 values('111_Bob','Bob');
Query OK, 1 row affected (0.14 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable2004;

This will produce the following output −

+-------------+----------+
| UserId      | UserName |
+-------------+----------+
| John_123    |     John |
| 23456_Carol |    Carol |
| 111_Bob     |      Bob |
+-------------+----------+
3 rows in set (0.00 sec)

Here is the query to create a stored procedure and select only a particular record with IN() −

mysql> delimiter //
mysql> create procedure test_of_in(IN uId varchar(20))
   BEGIN
      select * from DemoTable2004 where UserId IN(uId);
   END
   //
Query OK, 0 rows affected (0.15 sec)
mysql> delimiter ;
Call the stored procedure:
mysql> call test_of_in('23456_Carol');

This will produce the following output −

+-------------+----------+
| UserId      | UserName |
+-------------+----------+
| 23456_Carol | Carol    |
+-------------+----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.02 sec)

AmitDiwan

Published on 02-Jan-2020 05:48:05

• Related Questions & Answers
• Create a stored procedure to get the detail of a particular MySQL table stored in a database?
• How to correctly use DELIMITER in a MySQL stored procedure?
• Create a table inside a MySQL stored procedure and insert a record on calling the procedure
• Get database name from a query implemented in a MySQL Stored Procedure?
• How to use FOR LOOP in MySQL Stored Procedure?
• Set conditions in a MySQL stored procedure
• MySQL Stored Procedure to create a table?
• How can a MySQL stored procedure call another MySQL stored procedure inside it?
• How to correctly use delimiter in a MySQL stored procedure and insert values?
• How can we see the source code of a particular MySQL stored procedure?
• How to loop thrugh a stored procedure in MySQL?
• Create a stored procedure with delimiter in MySQL
• Implement Conditional MySQL Query in a stored procedure?
• Perform mathematical operations in a MySQL Stored Procedure?
• MySQL stored procedure to return a column value?