cursor not changing lines...
-
Hi,
I deal with the following problem:
I have inserts to do with data manipulation. For this specific case, I haven't found another way to solve it than a cursor.
Here's what's happening:
I open a cursor, select column 2, 4 and 5 from a table, and call a stored procedure with those values as input.
then I fetch next.
The thing is: value of column 5 always stays the one of the first select of cursor. Value of column 2 and 4 move together on the table, no problem.
How could it be that one value of the cursor doesn't follow the others?
There has to be something I haven't thought about, but 5 lines above, I use a cursor that works fine (for the other columns).
structure looks like:
OPEN c_st_eq
FETCH NEXT FROM c_st_eq INTO
@c_t_eq,
@co_st_eq, @n_st_eq
IF (@@FETCH_STATUS = -1)
BEGIN
CLOSE c_st_eq
DEALLOCATE c_st_eq
RETURN
END
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC tn_f
@c_t_eq, @co_st_eq, @n_st_eq
FETCH NEXT FROM c_st_eq INTO
@c_t_eq,
@co_st_eq, @n_st_eq
END
Any input appreciated,
Elno
-
How are you declaring the cursor and initializing it? Could you post the DECLARE cursor_name CURSOR FOR statement?
-
Are you sure that in the select that you use for the cursor , the last column has different values. Try printing the 3 variables inside the cursor to check.
WHILE (@@FETCH_STATUS = 0)
BEGIN
--EXEC tn_f
--@c_t_eq, @co_st_eq, @n_st_eq
PRINT @c_t_eq
PRINT @co_st_eq
PRINT @n_st_eq
FETCH NEXT FROM c_st_eq INTO
@c_t_eq,
@co_st_eq, @n_st_eq
END
-
Here it is:
DECLARE c_st_eq CURSOR
FOR
SELECT DISTINCT
c_t_eq,
co_st_eq, n_st_eq
FROM dbo.so_t_eq
WHERE co_st_eq IS NOT NULL
Elno
-
Absolutely certain that the value is not good from the start, because I do a 'print' before each exec.
The value that comes back all the time exists once in the source table (from a select).
Thanks for input,
Elno
Edited by - elno on 02/21/2003 2:31:26 PM
-
racosta, here is input about the select:
The value appears once in the source file.
It appears once in the source table (with a select * from table).
It appears once with a select * from table where value=@value.
Elno
Edited by - elno on 02/21/2003 3:00:23 PM
-
problem solved.
Thank you.
I don't know why, answers are faster to find after a post
-
quote:
problem solved.
I actually looked all over MSDN for a clue into this problem...couldn't find ANYTHING, which is why I never re'd back. But I'm still interested to see what the problem / solution was, if you wouldn't mind sharing...
-
Not at all.
Briefly, here is what I remember (80 hours ago already...). There was two cursors. I usually copy-paste the first 'fetch next to...' to the end of the cursor. For some reason, one letter didn't follow the others. So the first 'catch' was good. For the others, all the values but one were good. One didn't change, for the good reason it wasn't updated. I caught it by reseting all the values to NULL after executing the proc: one value didn't receive new input. And I didn't suspect this cause, because I did the cut and paste good (the letter missing was in the middle of the name).
So, that's not very spectacular, but the solution resides in the printing of the variables as often as possible in the proc, to catch the moment it's going wrong.
Elno
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply