SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE PROC dbo.PrintPrimeNumbers
--    @startnum int,
    @endnum int
AS 
WITH 
     t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    ,t256 AS (SELECT 0 AS n FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
    ,t16M AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) AS num FROM t256 AS a CROSS JOIN t256 AS b CROSS JOIN t256 AS c)
SELECT num
FROM t16M AS Dividend
WHERE
    Dividend.num <= @endnum
    AND NOT EXISTS(
        SELECT 1
        FROM t16M AS Divisor
        WHERE
            Divisor.num <= @endnum
            AND Divisor.num BETWEEN 2 AND SQRT(Dividend.num)
            AND Dividend.num % Divisor.num = 0
            AND Dividend.num <= @endnum
    )
;
-- EXEC dbo.PrintPrimeNumbers 1, 100;
EXEC dbo.PrintPrimeNumbers 100;


num


1

2

3

5

7

11

13

17

19

23

29

31

37

41

43

47

53

59

61

67

71

73

79

83

89

97


CREATE TABLE Prime (
    id INT NOT NULL IDENTITY PRIMARY KEY,
    prime INT NOT NULL UNIQUE
);
BEGIN TRANSACTION;
  INSERT INTO Prime (prime) VALUES (2);
COMMIT;
1 rows affected

CREATE TABLE LastOne (
    id INT NOT NULL PRIMARY KEY
);
BEGIN TRANSACTION;
  INSERT INTO LastOne VALUES (2);
COMMIT;
1 rows affected

CREATE PROC dbo.SieveOfEratosthenes AS
DECLARE
    @nextPrimeCandidate INT = NULL
BEGIN
  SET @nextPrimeCandidate = (
    SELECT id + 1
    FROM LastOne AS Dividend
    WHERE
      (Dividend.id + 1) % 2 = 1
      AND
      NOT EXISTS (
        SELECT 1
        FROM Prime
        WHERE
          (Dividend.id + 1) % prime = 0
    )
  )
  ;
  BEGIN TRANSACTION
    IF (@nextPrimeCandidate IS NOT NULL)
    INSERT INTO Prime (prime) VALUES (@nextPrimeCandidate);
    UPDATE LastOne SET id = id + 1;
  COMMIT;
END;
DECLARE @round INT = 1;
WHILE (@round < 10)
BEGIN
  EXEC dbo.SieveOfEratosthenes;
  SET @round += 1;
END;
13 rows affected

SELECT * FROM LastOne;
SELECT * FROM Prime;


id


11




id
prime


1
2

2
3

3
5

4
7

5
11


DECLARE @round INT = 1;
WHILE (@round < 10)
BEGIN
  EXEC dbo.SieveOfEratosthenes;
  SET @round += 1;
END;
12 rows affected

SELECT * FROM LastOne;
SELECT * FROM Prime;


id


20




id
prime


1
2

2
3

3
5

4
7

5
11

6
13

7
17

8
19


DECLARE @round INT = 1;
WHILE (@round < 100)
BEGIN
  EXEC dbo.SieveOfEratosthenes;
  SET @round += 1;
END;
SELECT * FROM LastOne;
SELECT * FROM Prime;


id


119




id
prime


1
2

2
3

3
5

4
7

5
11

6
13

7
17

8
19

9
23

10
29

11
31

12
37

13
41

14
43

15
47

16
53

17
59

18
61

19
67

20
71

21
73

22
79

23
83

24
89

25
97

26
101

27
103

28
107

29
109

30
113

id	prime
1	2
2	3
3	5
4	7
5	11
6	13
7	17
8	19
9	23
10	29
11	31
12	37
13	41
14	43
15	47
16	53
17	59
18	61
19	67
20	71
21	73
22	79
23	83
24	89
25	97
26	101
27	103
28	107
29	109
30	113

id	prime
1	2
2	3
3	5
4	7
5	11
6	13
7	17
8	19
9	23
10	29
11	31
12	37
13	41
14	43
15	47
16	53
17	59
18	61
19	67
20	71
21	73
22	79
23	83
24	89
25	97
26	101
27	103
28	107
29	109
30	113

id	prime
1	2
2	3
3	5
4	7
5	11
6	13
7	17
8	19
9	23
10	29
11	31
12	37
13	41
14	43
15	47
16	53
17	59
18	61
19	67
20	71
21	73
22	79
23	83
24	89
25	97
26	101
27	103
28	107
29	109
30	113