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>

[hwoess]

Re: EXEC Inside a cursor iteration problem

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]

Re: EXEC Inside a cursor iteration problem


"hwoess" <info23@iis-edv.at> wrote in message
news:40851c37$0 $12744$91cee783 @newsreader02.h ighway.telekom. at...[color=blue]
> 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[/color]
[...]

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

Re: EXEC Inside a cursor iteration problem

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]

Re: EXEC Inside a cursor iteration problem

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