 # Table Valued Function to return a range of prime numbers

,

I just wrote this function just for the fun of it.

It uses the sieve of Eratosthenes algorithm to find ranges of prime numbers and is a really fast method to generate lists of primes.

It also uses a dynamic inline tally table which is a modification of this http://www.sqlservercentral.com/articles/T-SQL/67899/ by Lynn Pettis

To get a list of primes between 1,000 and 10,000 just run the SQL statement:

SELECT * FROM dbo.DynamicPrimes(10000, 1000)

To find the count of primes between 2 and 100,000 just run:

SELECT COUNT(*) CountPrimes FROM dbo.DynamicPrimes(100000, 2)

etcetera...

```USE Test
GO
IF OBJECT_ID('[dbo].[DynamicPrimes]','TF') IS NULL
EXEC('CREATE FUNCTION [dbo].[DynamicPrimes]() RETURNS @t TABLE(n int) BEGIN RETURN END')
GO
-- *****************************************************************
-- Author:      Jonathan Roberts
-- Create date: 31/03/2017
-- Description: Table Valued Function to return all prime numbers within a range.
--
-- Sample Call:
-- To find the all the primes between 1 and 100,000:
--     SELECT * FROM dbo.DynamicPrimes(100000, 1)
-- To find the all the primes between 900,000 and 1,000,000:
--     SELECT * FROM dbo.DynamicPrimes(1000000, 00000)
-- To count the how many primes between 2 and 1,000,000
--      SELECT COUNT(*) CountPrimes FROM dbo.DynamicPrimes(1000000, 2)
-- *****************************************************************
ALTER FUNCTION dbo.DynamicPrimes
(
@Max int = 100000,
@Min int = 2
)
RETURNS @Primes TABLE (N int PRIMARY KEY CLUSTERED)
AS
BEGIN
IF @Min < 2 SET @Min = 2;

WITH L0(N) AS (SELECT 'Anything' FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS T(N)), -- 15 values
L1(N) AS (SELECT A.N FROM L0 A, L0 B, L0 C, L0 D, L0 E, L0 F, L0 G), -- 15^8  values (2562890625 more than enough for max value of int)
L2(N) AS (SELECT TOP (@Max) A.N FROM L1 A),
L3(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM L2)
INSERT INTO @Primes(N)
SELECT N
FROM (VALUES (2),(3),(5),(7),(11),(13),(17),(19),(23),(29),(31)) AS P(N)
UNION ALL
SELECT N
FROM L3
WHERE 0 NOT IN (N%2, N%3, N%5, N%7, N%11, N%13, N%17, N%19, N%23, N%29, N%31)
ORDER BY N

DECLARE @Prime as int
SET @Prime = 37 -- Initialise to first prime above already selected primes
WHILE @Prime <= SQRT(@Max) BEGIN

DELETE @Primes
WHERE N >= @Prime * @Prime
AND N % @Prime = 0

SELECT TOP(1) @Prime=N
FROM @Primes P
WHERE N > @Prime
ORDER BY N

END
DELETE @Primes
WHERE N < @Min

RETURN

END
GO```

4.67 (3)

4.67 (3)