Alan Lawhon
lawhonac at hiwaay.net
Fri Oct 28 13:30:48 CDT 2011
I'm almost embarrassed to post this, but after two days of trying to debug
this, I'm near the screaming point. (Ha! Ha! I know . real programmers
don't scream, but I'm new to SQL Server 2008R2 - Express Edition, so I'm
experiencing routine growing pains.)
I created a script to determine if a positive integer is a prime number - a
number divisible only by itself and 1. Fairly simple, right? I succeeded
in creating a script that tests a single hard-coded prime number candidate
and displays a SELECT statement indicating whether or not the integer is a
prime number. That script works like a champ. (If the prime number
candidate is an integer like 20 or 100 - which has multiple integer factors
that evenly divide into the candidate number, my script displays all of
those evenly divisible factors - along with a message that the number is not
prime.) So far, so good.
Next I decided to get cute and create a new "PRIME_NUMBERS_IN_A_RANGE.sql"
script that would find all the prime numbers within a given (hard-coded)
range - for instance, all the prime numbers in the range from 20 down to 3.
(There are seven prime numbers in that range.) This script is a little more
involved than the previous script, but it shouldn't be that hard - or so I
thought.
The code (with liberal doses of comments sprinkled in) follows. When I
attempt to execute this script, the first five SELECT statements (debugging
statements) output the expected values. For some reason, the sixth SELECT
statement does not appear to execute - the execution skips output of the:
SELECT CONVERT(varchar, @Prime_Number_Candidate) + ' Testing value of
@Prime_Number_Candidate B4 entering outer WHILE loop.'
SELECT statement and appears to go into an infinite loop. (The message in
the lower left-hand corner says "query executing" while the
time-of-execution clock continues ticking off. Also, despite the fact that
the first two statements in my script - "USE AP" (a sample database) and
"GO" should change the context to the "AP" sample database, the execution
display indicates "master" instead of "AP". (I don't know if this is
significant or not.) I tried commenting out "USE AP" and "GO" since I don't
use any objects from the AP sample database in this script, but I still get
the same "infinite loop" execution problem.
There's one other (long shot) possibility that may be at work here, but I
doubt if this is the real culprit - but I'll go ahead and mention it anyway.
There was a point, during one of the numerous "refining sessions" when I was
trying to get this to work, that I may have gotten an extraneous
(non-printing) character inserted into the script. (I recall the cursor
seeming to lock up and the entire editing screen going blank - as if all the
code statements had suddenly been wiped.) I was able to exit the editing
session and reenter the script and resume coding, but I can't shake the
feeling that something "bad" may have gotten inserted into the script and a
possible extraneous non-printing character (or combination of extraneous
non-printing characters if they do in fact exist) may be causing this
problem. This is a long shot, but I thought I should mention it - just in
case.
I suspect this is something "simple" that you guys have seen a thousand
times before - or at least once. I would be very grateful if you take a
quick look at this script and advise on what may be wrong. TIA.
Alan "Wrapped Around the Axle" Lawhon
PRIME_NUMBERS_IN_A_RANGE.sql
USE AP
GO
--
-- This version finds all prime numbers within a range of integers bound by
an upper -- limit and a lower limit. For instance, all prime numbers
between an upper limit -- of 20 and a lower limit of 3.
--
DECLARE @Prime_Number_Candidate AS int, @Outer_Loop_Index AS int, @Quotient
AS int, @Remainder AS int, @IS_PRIME_FLAG AS char(5)
DECLARE @Upper_Bound_Limit AS int, @Lower_Bound_Limit As int,
@Inner_Loop_Index As int
--
SET @Upper_Bound_Limit = 20
SET @Lower_Bound_Limit = 3
SET @Outer_Loop_Index = @Upper_Bound_Limit
SET @Prime_Number_Candidate = @Upper_Bound_Limit
--
--
SELECT @Prime_Number_Candidate As Test1
SELECT CONVERT(varchar, @Prime_Number_Candidate) + ' converted to
varchar.' AS Test2
SELECT CONVERT(varchar, @Upper_Bound_Limit) + ' Testing value of
@Upper_Bound_Limit B4 entering outer WHILE loop.'
SELECT CONVERT(varchar, @Lower_Bound_Limit) + ' Testing value of
@Lower_Bound_Limit B4 entering outer WHILE loop.'
SELECT CONVERT(varchar, @Outer_Loop_Index) + ' Testing value of
Outer_Loop_Index B4 entering outer WHILE loop.'
SELECT CONVERT(varchar, @Prime_Number_Candidate) + ' Testing value of
@Prime_Number_Candidate B4 entering outer WHILE loop.'
--
-- First five SELECT statements successfully execute - sixth SELECT
statement
-- does not execute.
--
--
-- Now we proceed with main (outermost) processing loop.
--
WHILE (@Outer_Loop_Index <= @Upper_Bound_Limit)
--
-- The outer loop WHILE expression sets up an infinite loop since the value
in the
-- @Outer_Loop_Index variable will always be less than or equal to the
value in the
-- @Upper_Bound_Limit - because I've set an explicit assignment statement
to ensure
-- that condition! Since we only want the outermost WHILE loop to process
prime
-- number candidates in the range from @Upper_Bound_Limit down to
Lower_Bound_Limit,
-- (in this case all the positive integers from 20 down to 3), we need to
put in a
-- logic test that will detect when the @Outer_Loop_Index goes below the
-- @Lower_Bound_Limit, (i.e. decrements from "3" to "2"), and exit the
script
-- at that point.
--
IF (@Outer_Loop_Index < @Lower_Bound_Limit)
SELECT 'Processing complete! Outer_Loop_Index = ' + CONVERT(varchar,
@Outer_Loop_Index) As Processing_Loop_Completed
IF (@Outer_Loop_Index < @Lower_Bound_Limit)
RETURN
--
-- Start processing with the assumption that the @Prime_Number_Candidate is
a prime
-- number - by setting the @IS_PRIME_FLAG to an initial value of 'TRUE' -
and
-- leaving the flag set to a value of 'TRUE' until it is determined that
the
-- integer is not prime.
--
SET @IS_PRIME_FLAG = 'TRUE'
--
BEGIN
--
SET @Inner_Loop_Index = @Prime_Number_Candidate
--
WHILE (@Inner_Loop_Index >= 1)
--
BEGIN
SET @Quotient =
@Prime_Number_Candidate/@Inner_Loop_Index
SET @Remainder =
@Prime_Number_Candidate%@Inner_Loop_Index
--
-- In any IF statement logic test, the first two identifiers connected by
the AND
-- logical operator, (i.e. @Quotient >= 1 AND @Remainder = 0) must be
included
-- since all non-prime divisors, (such as 10, 5, 4 and 2 in the case of
-- PRIME_CANDIDATE = 20) will be detected by this conditional expression.
-- The key is figuring out the correct third logical expression to add to
-- these first two conditions.
--
IF (@Quotient >= 1 AND @Remainder = 0 AND
@Prime_Number_Candidate <> @Inner_Loop_Index)
SELECT @Prime_Number_Candidate AS
Prime_Number_Candidate, @Inner_Loop_Index AS Divisor,
@Quotient AS Quotient, @Remainder AS Remainder,
CONVERT(varchar, @Prime_Number_Candidate) + ' is
NOT a prime number.',
CONVERT(varchar, @Prime_Number_Candidate) + ' is
evenly divisible by ' + CONVERT(varchar, @Inner_Loop_Index) + '.'
IF (@Quotient >= 1 AND @Remainder = 0 AND
@Prime_Number_Candidate <> @Inner_Loop_Index)
SET @IS_PRIME_FLAG = 'FALSE'
IF (@Quotient >= 1 AND @Remainder = 0 AND
@Prime_Number_Candidate <> @Inner_Loop_Index)
SELECT CONVERT(varchar, @IS_PRIME_FLAG) + ' flag
should be FALSE.' AS IS_PRIME_FLAG_VALUE
--
-- IF (@Inner_Loop_Index = 1 AND @IS_PRIME_FLAG =
'TRUE')
-- SELECT CONVERT(varchar,
@Prime_Number_Candidate) + ' is a PRIME number.'
--
SET @Inner_Loop_Index = @Inner_Loop_Index - 1
SELECT @Inner_Loop_Index AS 'Inner_Loop_Index value
after decrementing by 1.'
END
--
SET @Outer_Loop_Index = @Outer_Loop_Index - 1
--
SET @Inner_Loop_Index = @Outer_Loop_Index -- Watch for the
effect of this assignment statement.
--
SELECT CONVERT(varchar, @Outer_Loop_Index) + ' = Outer_Loop_Index
variable after decrementing by 1.'
SET @Prime_Number_Candidate = @Outer_Loop_Index
SELECT CONVERT(varchar, @Prime_Number_Candidate) + ' = next Prime
Number candidate.'
IF (@IS_PRIME_FLAG = 'TRUE')
SELECT CONVERT(varchar, @Prime_Number_Candidate) + ' is a PRIME
number.' AS Prime_Candidate
END