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

Previous Page Print Page