Open In App

Stored Procedure for prime numbers in MYSQL

Improve
Improve
Like Article
Like
Save
Share
Report

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

Last Updated : 22 Sep, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads