SQL Puzzle | Check Prime Number Puzzle

Puzzle Statement
The Puzzle is simple. You will be given an integer input value. You just have to check whether the number is prime or not using set based approach

Sample Input – 1

7

Expected output – 2

Prime Number

Sample Input – 2

12

Expected output – 2

Not a Prime Number

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION #

--


DECLARE @N AS INT = 7

SELECT 
	CASE WHEN COUNT(DISTINCT num1.number) > 0 THEN 'Not a Prime Number' ELSE 'Prime Number' END CheckPrime 
FROM MASTER..SPT_VALUES AS num1 
WHERE num1.Number > 1
	  AND  num1.Number < @N
	  AND  @N % num1.Number = 0
GO

DECLARE @N AS INT = 12

SELECT 
	CASE WHEN COUNT(DISTINCT num1.number) > 0 THEN 'Not a Prime Number' ELSE 'Prime Number' END CheckPrime 
FROM MASTER..SPT_VALUES AS num1 
WHERE num1.Number > 1
	  AND  num1.Number < @N
	  AND  @N % num1.Number = 0

GO


--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Kumar Khowal 

Http://MSBISkills.com