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