

SSCAddicted
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"




SSChampion
Group: General Forum Members
Last Login: Saturday, November 14, 2015 5:53 AM
Points: 13,873,
Visits: 9,610


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 flowoflogic 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 nonprime 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




SSCAddicted
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"




SSChampion
Group: General Forum Members
Last Login: Saturday, November 14, 2015 5:53 AM
Points: 13,873,
Visits: 9,610


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




SSCrazy Eights
Group: General Forum Members
Last Login: Friday, June 12, 2015 8:42 AM
Points: 9,294,
Visits: 9,496





Old Hand
Group: General Forum Members
Last Login: Friday, October 2, 2015 9:32 AM
Points: 362,
Visits: 2,076


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




SSCAddicted
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"




Ten Centuries
Group: General Forum Members
Last Login: Wednesday, October 28, 2015 7:43 AM
Points: 1,264,
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




SSChampion
Group: General Forum Members
Last Login: Saturday, November 14, 2015 5:53 AM
Points: 13,873,
Visits: 9,610


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




SSCrazy Eights
Group: General Forum Members
Last Login: Friday, June 12, 2015 8:42 AM
Points: 9,294,
Visits: 9,496




