cursor not changing lines...

Question

Post reply

cursor not changing lines...

elno

Old Hand

Points: 368
More actions
February 21, 2003 at 1:41 pm

#80295

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

Viewing 9 posts - 1 through 8 (of 8 total)

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

jpipes SSChampion Points: 12230 More actions · Answer 1

How are you declaring the cursor and initializing it? Could you post the DECLARE cursor_name CURSOR FOR statement?

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 2

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

elno Old Hand Points: 368 More actions · Answer 3

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

elno Old Hand Points: 368 More actions · Answer 4

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

elno Old Hand Points: 368 More actions · Answer 5

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

elno Old Hand Points: 368 More actions · Answer 6

problem solved.

Thank you.

I don't know why, answers are faster to find after a post

jpipes SSChampion Points: 12230 More actions · Answer 7

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...

elno Old Hand Points: 368 More actions · Answer 8

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