Write a MYSQL Script that creates and calls a stored procedu

Write a MYSQL Script that creates and calls a stored procedure named test. This procedure should identify all the prime numbers less than 100. Then it should display a string variable that includes the prime numbers like this:

2 | 3 | 5 | 7 | ...

Solution

CREATE PROCEDURE SP_nprimes(p_num int)

BEGIN

DECLARE i INT;

DECLARE j INT;

DECLARE nprimes INT;

DECLARE isprime INT;

SET i=2;

SET nprimes=0;

SET p_num=100;

main_loop:

WHILE(i<p_num) do

SET isprime=1;

SET j=2;

divisor_loop:

IF(MOD(i,j)=0) THEN

SET isprime=0;

LEAVE divisor_loop;

END IF;

SET j=j+1;

END WHILE;

IF(isprime=1) THEN

SET nprimes=nprimes+1;

END IF;

SET i=i+1;

END WHILE;

SELECT CONCAT(|nprimes|,\'prime numbers lessthan:\',p_num);

END;