SELECT LISTAGG(N,'&') WITHIN GROUP(ORDER BY N) PRIME_NO FROM( SELECT N FROM (SELECT LEVEL N FROM DUAL CONNECT BY LEVEL <=1000) WHERE N > 1 AND NOT EXISTS ( SELECT 1 FROM ( SELECT LEVEL D FROM DUAL CONNECT BY LEVEL <=1000 ) WHERE D >1 AND D < N AND MOD(N, D)=0 ) );

WITH RECURSIVE Numbers AS ( SELECT 2 AS n UNION ALL SELECT n + 1 FROM Numbers WHERE n < 1000 ), Divisors AS ( SELECT 2 AS d UNION ALL SELECT d + 1 FROM Divisors WHERE d < 31 -- 31 is approx sqrt(1000) ) SELECT GROUP_CONCAT(n SEPARATOR '&') AS PrimeNumbers FROM Numbers WHERE NOT EXISTS ( SELECT 1 FROM Divisors WHERE Divisors.d < Numbers.n AND Numbers.n % Divisors.d = 0 );

DELIMITER ##

CREATE PROCEDURE PRIME(IN P INT)
    BEGIN
    DECLARE I INT DEFAULT 2;
    O: WHILE I<=P DO
        DECLARE J INT ;
        DECLARE T INT;
        SET J = 2;
        INN: WHILE J <= SQRT(I) DO
            IF MOD(I, J) = 0 THEN 
                SET T = 0; 
                LEAVE INN;
            END IF; 
            SET T = 1;
            SET J = J +1;
        END WHILE INN;
    IF T = 1 THEN
        SELECT I ;
    END IF;
    SET I = I+1;
    END WHILE O;
END##

DELIMITER ;

CALL PRIME(1000);

this is not working, why? it is giving me an error of wrong syntax

Why did this fail?

DECLARE @i int = 0, @string varchar(4000) WHILE @i < 1001 BEGIN IF @i = 2 --Two is prime! BEGIN
SELECT @string = CONVERT(varchar(10),@i) SET @i += 1 CONTINUE END IF((@i % 2) = 0) --If not PRIME, then skip and continue on. BEGIN
SET @i += 1 CONTINUE END IF @string IS NULL --If first time iterating, don't add &. BEGIN SELECT @string = CONVERT(varchar(10),@i) SET @i += 1 CONTINUE END SELECT @string = @string + '&' + CONVERT(varchar(10),@i) --All other PRIME numbers. SET @i += 1
END PRINT @string;

MS SQL Server solution:

DECLARE @N INT = 1001;
WITH Numbers AS (
    SELECT 2 AS num
    UNION ALL
    SELECT num + 1
    FROM Numbers
    WHERE num + 1 <= @N
),
Primes AS (
    SELECT num
    FROM Numbers n
    WHERE NOT EXISTS (
        SELECT 1
        FROM Numbers d
        WHERE d.num < n.num
          AND d.num > 1
          AND n.num % d.num = 0
    )
)
SELECT STRING_AGG(CAST(num AS VARCHAR), '&') AS primes
FROM Primes
OPTION (MAXRECURSION 0);