Skip to content
Related Articles

Related Articles

Stored Procedure for prime numbers in MYSQL
  • Difficulty Level : Expert
  • Last Updated : 22 Sep, 2020

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
Recommended Articles
Page :