EXEC Inside a cursor iteration problem

[R. Z.]

I 've have a stored procedure that compares fields across databases.
In order to do so it requires 2 values it acquires from 2 tables. The
search is based on the ID of the data owner and a subject:

proc_evaluate_r esults @StudentId = '222222', Course = 'PSY101'

In order to obtain those values I run a cursor accross my records and
SELECT THEM INTO 2 @variables, which then replace 222222 and PSY101
with dynamic values eg.

--define a cursor etc.etc.

WHILE @@cursor_fetch = 0
BEGIN
--do the cursor call INTO @vars
EXEC proc_evaluate_r esults @StudentId = @studentID, @Course =
@CourseCode
END

Now,the vars are being passed to the stored procedure and executed OK,
but the cursor gets stuck on the last record and continues to evaluate
it until stopped manually.

If I comment out the EXEC and replace it with eg. PRINT @Course + ' |
' + @CourseCode it runs fine, exiting after the last record.
Thanks
R>

[R. Z.]

Hi and thank you for your reply.

The problem was logic actually and I should have caught that one
before posting. The PRINT statement was in the wrong place printing
the @count after it was incremented.

I will implement the SELECT instead of printing variables in my
debugging from now on though. It shaves off code required to maintain
the variables and is more direct eliminating problems like this one.

R>

[hwoess]

Hi,

i don't know why it runs okay with PRINT but not with EXEC
but if your program is like your description then you check @@cursor_fetch
before the cursor call - maybe this is your problem?

i would change this to:

--define a cursor etc.etc.
--do the first cursor call INTO @vars
WHILE @@cursor_fetch = 0
BEGIN
EXEC proc_evaluate_r esults @StudentId = @studentID, @Course =
@CourseCode
--do the cursor call INTO @vars
END

bye,
helmut

[Steven Wilmot]

"hwoess" <in****@iis-edv.at> wrote in message
news:40******** *************** @newsreader02.h ighway.telekom. at...

Hi,

i don't know why it runs okay with PRINT but not with EXEC
but if your program is like your description then you check @@cursor_fetch
before the cursor call - maybe this is your problem?

i would change this to:

--define a cursor etc.etc.
--do the first cursor call INTO @vars
WHILE @@cursor_fetch = 0

[...]

The preferred option is:

@@FETCH_STATUS , not @@CURSOR_FETCH

You also haven't indicated what is inside the WHILE loop (e.g. the structure
of your FETCH statements)

Steven

[R. Z.]

OK, thanks, putting the EXEC statement before the cursor call to
advance does work. Thank you,

New problem now however is that the first record does not get
processed while the last record is processed twice

TIA

Here's the code, PRINT statements and @count are for debug purposes:

DECLARE @CurEmployeeID CHAR(10), @CurCourseCode CHAR(10), @count AS
INT

SET @count = 1

DECLARE user_results_cu rsor CURSOR FOR
SELECT EmployeeID, CourseCode
FROM CourseUserData
ORDER BY EmployeeID

OPEN user_results_cu rsor
FETCH NEXT FROM user_results_cu rsor
INTO @CurEmployeeID, @CurCourseCode

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC proc_evaluate_r esults @EmployeeID = @CurEmployeeID, @CourseCode
= @CurCourseCode
FETCH NEXT FROM user_results_cu rsor
INTO @CurEmployeeID, @CurCourseCode
PRINT 'FETCH STATUS: ' + CAST(@@FETCH_ST ATUS AS VARCHAR) + ' | ' +
@CurEmployeeID + ' | ' + @CurCourseCode + ' | ' + CAST(@count AS
VARCHAR) + '* * * * * '
SET @count = @count + 1
END

CLOSE user_results_cu rsor
DEALLOCATE user_results_cu rsor

[hwoess]

i allways try to keep testing routines as simple as possible
so i would suggest to try the following:

DECLARE @CurEmployeeID CHAR(10), @CurCourseCode CHAR(10)
DECLARE user_results_cu rsor CURSOR FOR
SELECT EmployeeID, CourseCode FROM CourseUserData ORDER BY EmployeeID

OPEN user_results_cu rsor
FETCH NEXT FROM user_results_cu rsor INTO @CurEmployeeID, @CurCourseCode
WHILE @@FETCH_STATUS = 0 BEGIN
select @CurEmployeeID, @CurCourseCode
FETCH NEXT FROM user_results_cu rsor INTO @CurEmployeeID, @CurCourseCode
END
CLOSE user_results_cu rsor
DEALLOCATE user_results_cu rsor

so what do you see? No values from the first record but the last twice?
Then i would check the content of table CourseUserData!
(any difference if you use varchar(10) instead of char(10) for your
variables?)

bye,
Helmut

[R. Z.]

Hi and thank you for your reply.

The problem was logic actually and I should have caught that one
before posting. The PRINT statement was in the wrong place printing
the @count after it was incremented.

I will implement the SELECT instead of printing variables in my
debugging from now on though. It shaves off code required to maintain
the variables and is more direct eliminating problems like this one.

R>