Failed BETWEEN Operator

  • I could use some help resolving this problem.  My app uses two temporary tables @tmpTasks and @tmpView.  @tmpTasks stores values while I update fields before output. @tmpView stores the key values from @tmpTasks as I work on blocks of data.  My debug shows that the BETWEEN statement is not placing all values in @tmpView.  This data it taken immediately prior to using the IDs from @tmpView.  Althought 348 is in the table 349 in not included even though the BETWEEN operator is supposed to include the end values.  What is happening?

    --Debug of variables

    @BlockStart=348  @BlockEnd=349  @BlockMin=4

    --Debug of last 4 records of @tmpTasks

    ID  

    ------------

    346

    347

    348

    349

    --Debug of @tmpView

    ID         

    -----------

    348

    Code used to fill @tmpView

    ------------------------------

    INSERT INTO @tmpView (ID)

    SELECT ID FROM @tmpTasks

    WHERE ID BETWEEN @BlockStart AND @BlockEnd

  • At a wild guess, are the datatypes all the same (smallint or int)? No float or real or decimal?

  • All data types are int.  I've temporarily patched this problem with and IF NOT EXISTS(SELECT * FROM @tmpView WHERE ID=@BlockEnd) and then I insert the ID number.  I hate doing this but it was too expensive in time.  My intuition says it could be a Debug runtime problem because when I run some procs in Debug they don't return the same values the the stored proc does when I print values.

  • Just as a debugging question, did you run the alternate form:

    INSERT INTO @tmpView (ID)

    SELECT ID FROM @tmpTasks

    WHERE ID >= @BlockStart AND ID <= @BlockEnd

    If so, did it give similar or different results?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply