Create a Stored Procedure with MySQL and set a limit to display only a specific number of records
Let us first create a table −
mysql> create table DemoTable1368
-> (
-> ClientId int,
-> ClientName varchar(20)
-> );
Query OK, 0 rows affected (0.58 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable1368 values(101,'Adam');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable1368 values(102,'Bob');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable1368 values(103,'John');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable1368 values(104,'Sam');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable1368 values(105,'Mike');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable1368 values(106,'Carol');
Query OK, 1 row affected (0.11 sec)
Display all records from the table using select statement −
mysql> select * from DemoTable1368;
This will produce the following output −
+----------+------------+
| ClientId | ClientName |
+----------+------------+
| 101 | Adam |
| 102 | Bob |
| 103 | John |
| 104 | Sam |
| 105 | Mike |
| 106 | Carol |
+----------+------------+
6 rows in set (0.00 sec)
Following is the query to create a stored procedure and use MySQL LIMIT to limit the number of records to be displayed −
mysql> DELIMITER //
mysql> CREATE PROCEDURE limit_Demo(IN limitValue int)
-> BEGIN
-> DECLARE limValue int;
-> SET limValue =limitValue ;
-> SELECT * FROM DemoTable1368 LIMIT limValue;
-> END
-> //
Query OK, 0 rows affected (0.17 sec)
mysql> DELIMITER ;
Now you can call the stored procedure using call command −
mysql> CALL limit_Demo(3);
This will produce the following output −
+----------+------------+
| ClientId | ClientName |
+----------+------------+
| 101 | Adam |
| 102 | Bob |
| 103 | John |
+----------+------------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.03 sec)
Published on 08-Nov-2019 11:06:45
