CREATE proc [dbo].[usp_FindPrimes] (@opt tinyint, @ResultCount int)
AS
declare @NextInt int--This is the next number to check
declare @Count int--Count how many primes to find
declare @BaseCount int--Used to initially check the table
if exists (select * from sys.objects where type = 'u' and name = 'Primes')
drop table Primes
CREATE TABLE [dbo].[Primes](
[Prime_No] [int] NOT NULL,
CONSTRAINT [PK_Prime] PRIMARY KEY CLUSTERED
(
[Prime_No] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Insert into Primes (Prime_No) values (2)
Insert into Primes (Prime_No) values (3)
truncate table PRIMES
Select @BaseCount = count(Prime_No) from primes where Prime_No in (2,3)
If @BaseCount <> 2 begin
Delete from primes where Prime_No in (2,3)
insert into primes (Prime_No) values (2)
insert into primes (Prime_No) values (3)
end
select @NextInt = max(Prime_No) + 2
from Primes
set @Count = 0
while @Count < 5000 begin
if not exists(
Select Prime_No
from Primes
where @NextInt % Prime_No = 0
and sqrt(@NextInt) >= Prime_No)
begin
insert into Primes(Prime_No) select @NextInt
set @Count = @Count + 1
end
set @NextInt = @NextInt + 2
end
if @opt = 1
select top (@ResultCount) * from Primes ORDER BY Prime_No asc
else if @opt = 2
select top (@ResultCount) * from Primes ORDER BY Prime_No desc
else if @opt = 3
begin
select top (@ResultCount) * INTO #Temp FROM Primes ORDER BY Prime_No asc
SELECT * FROM #Temp ORDER BY Prime_No desc
end
return
go
Then execute
exec usp_findprimes 3,10