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))
Begin
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 */
if(j%i=0)then
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;

End
$$

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

My Personal Notes arrow_drop_up

Save