SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating Stored Procedure in SQL server 2000 for Printing Prime Numbers


Creating Stored Procedure in SQL server 2000 for Printing Prime Numbers

Author
Message
SQLFanatic
SQLFanatic
Mr or Mrs. 500
Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)

Group: General Forum Members
Points: 528 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"
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23341 Visits: 9730
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
SQLFanatic
SQLFanatic
Mr or Mrs. 500
Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)

Group: General Forum Members
Points: 528 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"
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23341 Visits: 9730
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
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14646 Visits: 9518
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."
Ross McMicken
Ross McMicken
Mr or Mrs. 500
Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)

Group: General Forum Members
Points: 547 Visits: 2234
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


SQLFanatic
SQLFanatic
Mr or Mrs. 500
Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)

Group: General Forum Members
Points: 528 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"
Ray Hastie
Ray Hastie
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1274 Visits: 142
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


GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23341 Visits: 9730
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
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14646 Visits: 9518
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."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search