August 15, 2005 at 8:52 am
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
August 15, 2005 at 9:11 am
At a wild guess, are the datatypes all the same (smallint or int)? No float or real or decimal?
August 15, 2005 at 4:27 pm
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.
August 15, 2005 at 6:22 pm
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