Odd numeric comparisions in Stored Proc

[mdlueck]

Working to debug the corresponding UDF... ( http://www.access-programmers.co.uk/forums/showthread.php?t=230186 ) I saw some very odd behavior the UDF was exhibiting. I converted it over to a SP so I could place PRINT statements within the code.

What I am seeing is that a smallint value of 100 is being considered between 0 and 99. Hua!?!?!?!?

Further odd point, I needed to wrap fTRIM around the string representation of the input smallint, else I ended up with perhaps 8 characters of white space on the left side of the number. Should I have needed to use fTRIM to clean the "white space from nowhere?"

Driver Script:

-- Define parameters the SP requires
DECLARE @sintInput smallint;
SET @sintInput = 100;
PRINT '@sintInput=>' + dbo.fTRIM(STR(@sintInput)) + '<'
EXEC dbo.sprevconv_ConvN2A @sintInput;

Messages output:

@sintInput=>100<
@sintInput=>100<
(@sintInput >= 0) OR (@sintInput <= 99)

Results output:

rev
10

The current debug code in Stored Procedure format:

:setvar SPNAME sprevconv_ConvN2A
USE [Fandango]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Check if the STORED PROCEDURE exists already, DROP if found
IF EXISTS(SELECT * FROM [sys].[objects] WHERE [type_desc] = 'SQL_STORED_PROCEDURE' AND [name] = '$(SPNAME)')
  DROP PROCEDURE [dbo].[$(SPNAME)]
GO
CREATE PROCEDURE [dbo].[$(SPNAME)] (
  -- Add the parameters for the stored procedure here
  @sintInput AS smallint
  )
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
-- Insert statements for user-defined function here
  DECLARE @sintN smallint
  DECLARE @sintR smallint
  DECLARE @strRC varchar(2)

[COLOR=Blue]  PRINT '@sintInput=>' + dbo.fTRIM(STR(@sintInput)) + '<'[/COLOR]
  IF (@sintInput < 0) OR (@sintInput > 801)
    BEGIN
      PRINT '(@sintInput < 0) OR (@sintInput > 801)'
      SET @strRC = NULL
    END
[COLOR=Blue]  ELSE IF (@sintInput >= 0) OR (@sintInput <= 99)
    BEGIN
      PRINT '(@sintInput >= 0) OR (@sintInput <= 99)'
      SET @strRC = dbo.fTRIM(STR(@sintInput))
    END
[/COLOR]  ELSE IF (@sintInput > 125)
    BEGIN
      PRINT '(@sintInput > 125)'
      --First subtract the first 99 numeric Revision numbers
      SET @sintInput = @sintInput - 99
      SET @sintR = (@sintInput % 26) + 64
      IF (@sintR = 64)
        BEGIN
          PRINT '(@sintR = 64)'
          SET @sintN = ((@sintInput / 26) + 64) - 1
          SET @sintR = 90
        END
      ELSE
        BEGIN
          PRINT 'ELSE NOT (@sintR = 64)'
          SET @sintN = ((@sintInput / 26) + 64)
        END
        SET @strRC = ASCII(@sintN) + ASCII(@sintR)
    END
  ELSE
    BEGIN
      PRINT 'FINAL ELSE'
      --First subtract the first 99 numeric Revision numbers
      SET @sintInput = @sintInput - 99
      SET @strRC = ASCII(@sintInput + 64)
    END

-- Define a query to prepare the output value based on the variables which have been prepared
SELECT @strRC AS [rev]

SET NOCOUNT OFF
END

 

[mdlueck]

And the nonsense persisted with input of 101, 102... so I hauled off and clobbered it with 999! And...

Messages output

@sintInput=>999<
@sintInput=>999<
(@sintInput < 0) OR (@sintInput > 801)

Results output:

rev
NULL

Suggestion?

 

[mdlueck]

Well in further testing, the first IF statement is being evaluated correctly...

801 is the highest number allowed to pass, 802 is caught and handled correctly.

So then why would the very next IF statement be evaluated incorrectly?

Wait a minute... the next one is greater-than-or-equal-to / less-than-or-equal-to... I wonder if that is making a difference... hhhmmm.... Nope, I removed the = from the >= and <= and 100 is still considered between 0 and 99.

Still searching...

 

[mdlueck]

04:37 this morning I realized the problem... VBA code in that spot had logical AND, and I put OR into the SQL code.

That solved, moving on debugging this SQL code further.

 

[WayneRyan]

Michael,

IF (@sintInput >= 0) AND (@sintInput <= 99)

The initial formatting made this very hard for me to read.
I hate the Structure of If-Then-Else blocks in T-SQL.

:setvar SPNAME sprevconv_ConvN2A
USE [Fandango]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Check if the STORED PROCEDURE exists already, DROP if found
IF EXISTS(SELECT * FROM [sys].[objects] WHERE [type_desc] = 'SQL_STORED_PROCEDURE' AND [name] = '$(SPNAME)')
  DROP PROCEDURE [dbo].[$(SPNAME)]
GO
CREATE PROCEDURE [dbo].[$(SPNAME)] (
  -- Add the parameters for the stored procedure here
  @sintInput AS smallint
  )
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
-- Insert statements for user-defined function here
  DECLARE @sintN smallint
  DECLARE @sintR smallint
  DECLARE @strRC varchar(2)

  PRINT '@sintInput=>' + dbo.fTRIM(STR(@sintInput)) + '<'
  IF (@sintInput < 0) OR (@sintInput > 801)
    BEGIN
      PRINT '(@sintInput < 0) OR (@sintInput > 801)'
      SET @strRC = NULL
    END
  ELSE IF (@sintInput >= 0) OR (@sintInput <= 99)			-- This whole block
         BEGIN								-- is the Else clause
           PRINT '(@sintInput >= 0) OR (@sintInput <= 99)'		-- of the above IF
           SET @strRC = dbo.fTRIM(STR(@sintInput))			--
         END								--
       ELSE IF (@sintInput > 125)					--
              BEGIN							--
                PRINT '(@sintInput > 125)'				--
                --First subtract the first 99 numeric Revision numbers	--
                SET @sintInput = @sintInput - 99			--
                SET @sintR = (@sintInput % 26) + 64			--
                IF (@sintR = 64)					--
                   BEGIN						--
                     PRINT '(@sintR = 64)'				--
                     SET @sintN = ((@sintInput / 26) + 64) - 1		--
                     SET @sintR = 90					--
                   END							--
               ELSE							--
                 BEGIN							--
                   PRINT 'ELSE NOT (@sintR = 64)'			--
                   SET @sintN = ((@sintInput / 26) + 64)		--
                 END							--
              SET @strRC = ASCII(@sintN) + ASCII(@sintR)		--
              END							--
          ELSE								--
            BEGIN							--
              PRINT 'FINAL ELSE'					--
              --First subtract the first 99 numeric Revision numbers	--
              SET @sintInput = @sintInput - 99				--
              SET @strRC = ASCII(@sintInput + 64)			--
            END

-- Define a query to prepare the output value based on the variables which have been prepared
SELECT @strRC AS [rev]

SET NOCOUNT OFF
END

hth,
Wayne

 

[WayneRyan]

Oops ... How did not see that you had solved it.

Wayne

 

[mdlueck]

Better late than never, Wayne! Thank you.