/******************************************************
 *
 * Name:         primes.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     06-12-2012
 *     Purpose:  A program to generate prime
 *               numbers.
 * 
 ******************************************************/


/*  
	Create a database to hold the prime numbers
*/


-- Which database to use.
USE [master]
GO

-- Delete existing databases.
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'MATH')
DROP DATABASE MATH
GO

-- Add new databases.
CREATE DATABASE MATH ON  
 PRIMARY 
  ( NAME = N'TRAN_PRI_DAT', FILENAME = N'C:\MSSQL\DATA\MATH.MDF' , SIZE = 1024MB, FILEGROWTH = 128MB) 
 LOG ON 
  ( NAME = N'TRAN_ALL_LOG', FILENAME = N'C:\MSSQL\LOG\MATH.LDF' , SIZE = 512MB, FILEGROWTH = 128MB)
GO



/*  
	Create a table to hold the primes
*/

-- Which database to use.
USE [MATH]
GO

-- Delete existing table
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DBO].[TBL_PRIMES]') AND type in (N'U'))
DROP TABLE [DBO].[TBL_PRIMES]
GO

-- Add new table
CREATE TABLE [DBO].[TBL_PRIMES] (
	[MY_VALUE] [bigint] NOT NULL,
	[MY_TIME] [datetime] NOT NULL DEFAULT (GETDATE())
) 
GO

-- Delete existing index
IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IDX_PRIMES')
    DROP INDEX IDX_PRIMES ON [DBO].[TBL_PRIMES];
GO

-- Add new index
CREATE INDEX IDX_PRIMES 
    ON [DBO].[TBL_PRIMES] ([MY_VALUE]); 
GO



/*  
	Create a procedure to determine if number is prime
*/


-- Which database to use.
USE [MATH]
GO

-- Delete existing procedure
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IS_PRIME]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[IS_PRIME]
GO

-- Create the stored procedure from scratch
CREATE PROCEDURE IS_PRIME
    @VAR_NUM2 BIGINT
AS
BEGIN
    -- LOCAL VARIABLES
    DECLARE @VAR_CNT2 BIGINT;
    DECLARE @VAR_MAX2 BIGINT;

    -- NOT A PRIME NUMBER
    IF (@VAR_NUM2 = 1)
        RETURN 0;            

    -- A PRIME NUMBER
    IF (@VAR_NUM2 = 2)
        RETURN 1;            

    -- SET UP COUNTERS    
    SELECT @VAR_CNT2 = 2;
    SELECT @VAR_MAX2 = SQRT(@VAR_NUM2) + 1;

    -- TRIAL DIVISION 2 TO SQRT(X)
    WHILE (@VAR_CNT2 <= @VAR_MAX2)
    BEGIN
        -- NOT A PRIME NUMBER
        IF (@VAR_NUM2 % @VAR_CNT2) = 0
            RETURN 0;            

        -- INCREMENT COUNTER
        SELECT @VAR_CNT2 = @VAR_CNT2 + 1;
        
    END;

    -- NOT A PRIME NUMBER
    RETURN 1;
    
END
GO



/*  
	Create a procedure to store primes from x to y.
*/

-- Which database to use.
USE [MATH]
GO

-- Delete existing procedure
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STORE_PRIMES]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[STORE_PRIMES]
GO

-- Create the stored procedure from scratch
CREATE PROCEDURE STORE_PRIMES
    @VAR_ALPHA BIGINT,
    @VAR_OMEGA BIGINT
AS
BEGIN
    -- DECLARE VARIABLES
    DECLARE @VAR_CNT1 BIGINT;
    DECLARE @VAR_RET1 INT;
    
    -- SET VARIABLES
    SELECT @VAR_RET1 = 0;
    SELECT @VAR_CNT1 = @VAR_ALPHA;

    -- CHECK EACH NUMBER FOR PRIMENESS
    WHILE (@VAR_CNT1 <= @VAR_OMEGA)
    BEGIN
        -- ARE WE PRIME?
        EXEC @VAR_RET1 = DBO.IS_PRIME @VAR_CNT1;
        
        -- FOUND A PRIME
        IF (@VAR_RET1 = 1)
          BEGIN
            INSERT INTO [DBO].[TBL_PRIMES] (MY_VALUE) VALUES (@VAR_CNT1);
            PRINT @VAR_CNT1;
          END        
        ELSE        
          BEGIN
            PRINT '...';
          END;
        
        -- INCREMENT COUNTER
        SELECT @VAR_CNT1 = @VAR_CNT1 + 1        
    END;
    
END
GO


/*  
	Calculate primes between 1 & 5 Million
*/


EXEC STORE_PRIMES 1, 5000000



/*  
	Total time in seconds (1275)
*/

SELECT DATEDIFF(s, DT.START, DT.FINISH) AS TOTAL
FROM (SELECT MIN(MY_TIME) AS START, MAX(MY_TIME) AS FINISH FROM dbo.TBL_PRIMES) DT