How to Print Prime Numbers in MS SQL Server?

In this article, we are going to print Prime numbers using MS SQL. Here we will be using 2 while loops statement for printing prime numbers.

Steps 1: First we will DECLARE a variable I with initial value 2.

Query:

DECLARE @I INT=2

Step 2: Then we will DECLARE a variable PRIME with an initial value of 0 (this will set the value of PRIME).

Query:

DECLARE @PRIME INT=0

Step 3: Table Definition

We will create a temporary table variable that will hold prime numbers (using DECLARE and TABLE keywords).

Query:

DECLARE @OUTPUT TABLE (NUM INT)

Step 4: Now we will use nested while loop, same as we write a program for prime numbers.

Query:

DECLARE @I INT=2
DECLARE @PRIME INT=0
DECLARE @OUTPUT TABLE (NUM INT)
WHILE @I<=100
BEGIN
    DECLARE @J INT = @I-1
    SET @PRIME=1
    WHILE @J>1
    BEGIN
        IF @I % @J=0
        BEGIN
            SET @PRIME=0
        END
        SET @J=@J-1
    END
    IF @PRIME =1
    BEGIN
        INSERT @OUTPUT VALUES (@I)
    END
    SET @I=@I+1
END
SELECT * FROM @OUTPUT

Explanation:

In the First while loop, we will DECLARE the initial value of I as 100, which means this loop will provide us the prime numbers between 2 and 100.
Now, we will declare J with the initial value as I-1. As shown in the above code.
Then, a second while loop will be inserted which will run until J is greater than 1.
if the statement is there with condition @I % @J = 0, which means when the remainder of I/J is 0 then PRIME will be set to 0, and the value of J is decremented by 1.
If at the end of loop PRIME is set to 1, then that number will be inserted in our OUTPUT TABLE using the below code.

Query:

INSERT @OUTPUT VALUES (@I)

And then another loop will start for the next number.

Step 5: Suppose that I have an initial value of 4, i.e I = 4.

Now in the second loop J will have an initial value of I-1 which is 3.
By 4%3 we will get 1 so Prime will be set to 1 as before and J is decremented by 1.
Now for 4%2, we will get 0, Now as per our condition, PRIME will be set to 0. Since 4 has more factors than 1 and itself, the loop will be started again with an incremented value of I, that is 5(I+1).

Output: Below output for I<=100 which means it will print prime numbers from 2 to 100.

Article Tags :

SQL

SQL-Server