Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Creating Stored Procedure in SQL server 2000 for Printing Prime Numbers Expand / Collapse
Author
Message
Posted Wednesday, April 08, 2009 1:42 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, August 16, 2013 1:51 PM
Points: 472, Visits: 371
I have come up with this code(given below) in an effrot to create an stored procedure that will print the prime numbers between 1 and 500.

The problem is that the execution never stops when this code is executed. I am not sure if this approach is right or the code is wrong as a whole. When I change the position of (@i=@i+1) and place it after the 'END' word where it(@i=@i+1) is now, I get the only number 2 printed under messages tab and the execution still countinues.

I have worked on this for an ample amount of time now and thought of seeking valuable advice rather than scratching my head furtheron.

Your responses will be appreciated.
sincerely thankfully.

/******** PART OF THE STORED PROCEDURE ********/
DECLARE @i INT, @a INT, @count INT

SET @i = 1
WHILE (@i <= 500)
BEGIN
SET @count = 0
SET @a = 1
WHILE (@a <= @i)
BEGIN
IF (@i%@a=0)
BEGIN
SET @count = @count + 1
SET @a = @a + 1
END
IF (@count = 2)
BEGIN
PRINT @i
SET @i = @i+1
END
END

END


________________________________________________________________
"The greatest ignorance is being proud of your learning"
Post #693521
Posted Wednesday, April 08, 2009 1:58 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
While I'm assuming this is a homework problem, and I normally don't help with those, this was interesting enough that I couldn't stop myself.

You have a number of flow-of-logic errors. For example, if you look at where you have your increment on @a, you'll notice that you'll never reach that if @i is not evenly divisible by @a. So, the moment you try to divide 3 by 2, it never increments @a to 3, but just loops. Even if you fix that, check what'll happen to your increment for @i when it hits 4. Since 4 is the first non-prime number, it has a problem with where the increment is in relation to the check for @count.

Try this version:
DECLARE
@i INT,
@a INT,
@count INT

SET @i = 1
WHILE (@i <= 500)
BEGIN
SET @count = 0

SET @a = 1

WHILE (@a <= @i)
BEGIN
IF (@i % @a = 0)
SET @count = @count + 1

SET @a = @a + 1
END

IF (@count = 2)
PRINT @i

SET @i = @i + 1
END



- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #693538
Posted Wednesday, April 08, 2009 2:31 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, August 16, 2013 1:51 PM
Points: 472, Visits: 371

ha ha !! That laugh was for your(@GSquared) assumption being true. Yes, it is a home work. But I did tried my best before seeking help and was looped in between 'BEGIN' and 'END' since I am new to the SQL platform.


Well, your solution worked perfectly !! I shall thank you for that, for your time spent on that.

And the quote is educative and nice too..


________________________________________________________________
"The greatest ignorance is being proud of your learning"
Post #693566
Posted Wednesday, April 08, 2009 2:36 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
A useful trick to Begin...End blocks, is indent everything inside them by one tab. Helps you see the flow and make sure that you actually get where you need to.

Glad I was able to both amuse and help you.

For future reference, if you have something that's homework, it's considered ethical to state that right up front in the question. Doing so, and showing what you've done already to try, just keeps it honest, and everyone likes that.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #693576
Posted Wednesday, April 08, 2009 10:33 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
Didn't we do set-based prime sieves last year sometime? ...

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #693729
Posted Sunday, April 12, 2009 4:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:33 AM
Points: 357, Visits: 1,928
And, to make it a bit faster, let's skip all of the even numbers after 2 (I suspect I've chosen a less than desirable way to do that)

DECLARE
@i INT,
@a INT,
@count INT
SET @i = 1
WHILE (@i <= 500)
BEGIN
SET @count = 0
SET @a = 1
WHILE (@a <= @i)
BEGIN
IF (@i % @a = 0)
SET @count = @count + 1
SET @a = @a + 1
END
IF (@count = 2)
PRINT @i
SET @i = @i + case when @i < 3 then 1 else 2 end
END

Post #695412
Posted Sunday, April 12, 2009 2:14 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, August 16, 2013 1:51 PM
Points: 472, Visits: 371
That's another useful condition we can use here to increase performance. As the query without the case condition takes a while to get executed.Your post is appreciated !!
Thanks @Ross.M


________________________________________________________________
"The greatest ignorance is being proud of your learning"
Post #695498
Posted Tuesday, April 14, 2009 2:20 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 16, 2013 7:07 AM
Points: 1,117, Visits: 137
I have been thinking about this topic in the back of my brain since I read the post this morning.

The initial attempt was good for a first attempt but the brute force method would not be best. By definition, the only values that we need to worry about are prime numbers for the check, since all other values would be divisible by these.

Using a Temp Table to store the primes, we could optimize as such:
(To get meaningful results, I increased the search to 10,000. The original algorithm took 170 seconds on an old test box. This optimized routine took 2 seconds. 100,000 numbers took 112 seconds)

DECLARE 
@Max INT, -- Max number for prime search
@i INT, -- Counter for possible candidates
@count INT , -- row count of matches
@TimeStart DateTime , -- Time execution start

SET NOCOUNT ON

-- Create Temp Table for storing results
Create Table #Primes(PrimeNumber Int Not Null)

-- Initialize variables
SET @TimeStart = GetDate()
SET @Max = 10000
SET @count = 0
SET @i = 2

WHILE (@i <= @Max)

BEGIN
SET @count = (SELECT COUNT(*) FROM #Primes WHERE (@i % PrimeNumber) = 0 )

If @count = 0
INSERT INTO #Primes(PrimeNumber) Values(@i)

SET @i = @i + 1

END

-- Output the results
SELECT * FROM #primes

-- output execution in seconds
print DateDiff(ss, GetDate(), @TimeEnd)

DROP TABLE #Primes

Post #697023
Posted Tuesday, April 14, 2009 3:04 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
Of course, the real way to get this kind of thing from SQL is to use sets. Like this:
set nocount on;
create table #Numbers (
Number smallint identity primary key);
go
insert into #Numbers
default values;
go 500

select N1.Number
from #Numbers N1
inner join #Numbers N2
on N1.Number%N2.Number = 0
group by N1.Number
having count(*) <= 2
order by N1.Number;



- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #697077
Posted Tuesday, April 14, 2009 3:14 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
I'm glad someone finally said that, Gus. All of my prime sieve stuff is in heavily nested CTE's and I did not want to to have to retrovert it to SQL 2000.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #697089
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse