Stored Procedure for prime numbers in MYSQL

In this article, you will see how you can write the logic of stored procedure to generate prime numbers for any given input.

Title :
Given a number N print all the prime number (<=N) separated by comma (, ) using Stored Procedure in MYSQL.

Example-1 :

Input : N = 10
Output : 2, 3, 5, 7

Example-2 :

Input : N = 20
Output : 2, 3, 5, 7, 11, 13, 17, 19

Program :
A program to demonstrate that how you can generate the prime numbers for any given input.

delimiter $$
create procedure getPrime(IN n int, OUT result varchar(200))
declare j, i, flag int;  /* Declare variables */
set j:=2; 
set result:=' '; 
while(j<n) do /* Loop from 2 to n */
set i:=2;
set flag:=0;

while(i<=j) do /* Loop from 2 to j */
set flag:=flag+1;
end if;
set i:=i+1; /* Increment i */
end while;

if (flag=1) then
set result:=concat(result, j, ', '); 
/* Concat the prime number with ', ' */
end if ;
set j:=j+1; /* Increment j */
end while;


How to call the procedure :
To call the procedure used the following query given below.

call getPrime(20, @result);
select substr(@result, 1, length(@result)-1); /* To remove last character */

Output :

2, 3, 5, 7, 11, 13, 17, 19

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using or mail your article to See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.

Article Tags :
Practice Tags :


Please write to us at to report any issue with the above content.