How to identify Prime numbers in SQL Server

While on HackerRank puzzles, I came through a question to find out prime numbers in SQL Server. Here is my try as below. You may try your own and share the code in the comment.
```declare @max INT = 1000,@s varchar(max) =''

;WITH Tally_Numbers(n) AS
(
SELECT 2
UNION ALL
SELECT n+1 FROM Tally_Numbers WHERE n <= @max
)
,cte1 as(
select Tally1.n
from Tally_Numbers Tally1
where not exists (select 1 from Tally_Numbers Tally2 where Tally2.n < Tally1.n AND Tally1.n % Tally2.n = 0)
)
SELECT STUFF((
SELECT '&' + CAST(r.N AS VARCHAR(10)) AS "text()"
FROM cte1 r
ORDER BY r.N
FOR XML PATH(''),TYPE).value('./text()[1]','VARCHAR(8000)'),1,1,'')
OPTION (MAXRECURSION 0);
```

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

One thought on “How to identify Prime numbers in SQL Server”

1. PopeScooby says:

This is what I Used…

DECLARE @cnt INT = 2;
DECLARE @resp varchar(1000) = ”;

WHILE @cnt <= 1000
BEGIN

Declare @result bit = 1,@i int = 2
While (@i<@cnt)
Begin
if(@cnt % @i = 0)
Begin
Set @result = 0
break
End
Set @i += 1
End

If @result = 1
Begin
DECLARE @cnt_str varchar(50) = Cast(@cnt as varchar(50))

If @resp = ''
Begin
SET @resp = @resp + @cnt_str
END
Else
Begin
SET @resp = @resp + '&' + @cnt_str
End;
End
SET @cnt = @cnt + 1;

END;
Select @resp;

Like