Question

SQL Code:

Write a script that creates and calls a stored procedure named test. This procedure should identify all of the prime numbers less than 100. (A prime number is an integer that can't be divided by another integer other than 1 and itself.) Then, it should display a string variable that includes the prime numbers like this:

2 1 3 1 5 1 7 1 1 1 1 1 3 1 1 7 1 1 9 1 2 3 1 2 9 1 3 1 1 . . .

Hint: To get this to work, you will need to nest one loop within another loop. In addition, you will need to code an IF statement within the inner loop.

Answer 1

SQL code screenshot (Create Stored Procedure test):

SQL code (Create Stored Procedure test):

CREATE PROCEDURE test
AS
        DECLARE @primeValue INT = 3, @range INT = 100
        DECLARE @value INT, @result VARCHAR(MAX) = '2'
        DECLARE @flag BIT

        -- This loop will iterate from 3 to 100
        WHILE @primeValue <= @range
        BEGIN
                SET @value = SQRT(@primeValue)
                SET @flag = 0
                
                -- This loop will check if current number is prime or not
                WHILE @value >= 2
                BEGIN
                        -- If divisible by the value then not a prime number
                        IF @primeValue % @value = 0
                        BEGIN
                                SET @flag = 1
                                BREAK
                        END
                        SET @value = @value - 1;
                END

                -- Adds the prime number to the result with '1' as a seperator
                IF @flag = 0
                BEGIN
                        SET @result = @result + '1' + CAST(@primeValue AS VARCHAR(3));
                END
                
                -- Getting next value
                SET @primeValue = @primeValue + 1;
        END


        -- Below code will add space in between each character
        
        -- location where we want first space 
        DECLARE @pos INT = 2 
        -- to seperate each character with a space
        DECLARE @separator nvarchar(5) = ' '
    WHILE @pos < LEN(@result)+1 
    BEGIN 
                -- Inserts a space at the given position & re-assigns the result
        SET @result = STUFF(@result, @pos, 0, @separator); 
        SET @pos = @pos+2; 
    END 

    PRINT @result;

SQL code (Call Stored Procedure test): Below code to execute the stored procedure:

EXEC test;

Result: