Create a Stored Procedure with MySQL and set a limit to display only a specific number of records

MySQL Database Training for Beginners

39 Lectures 5.5 hours

Simon Sez IT

More Detail

Python programming with MySQL database: from Scratch

152 Lectures 16 hours

Metla Sudha Sekhar

More Detail

Learn MySQL from scratch for Data Science and Analytics

87 Lectures 5.5 hours

Metla Sudha Sekhar

More Detail

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)

AmitDiwan

Updated on 08-Nov-2019 11:06:45

• Related Questions & Answers
• Display table records from a stored procedure in MySQL
• MySQL RegExp to fetch records with only a specific number of words
• Create a stored procedure with delimiter in MySQL
• MySQL Stored Procedure to create a table?
• Set conditions in a MySQL stored procedure
• Display description of MySQL stored procedure
• Fetch ordered records after a specific limit in MySQL
• Create a table inside a MySQL stored procedure and insert a record on calling the procedure
• MySQL Stored Procedure to update records with certain condition?
• Create a stored procedure to get the detail of a particular MySQL table stored in a database?
• Use IN() to get only a particular record in a MySQL stored procedure?
• How to display message from a stored procedure?
• Display only a specific duplicate record in MySQL
• How to set two variables in a stored procedure with a single MySQL select statement?
• Display records from MySQL stored Procedure with IF…THEN…END IF statements