Cursor returns last row twice 1

[Dudek]

Hey there..

Basically, as the subject says, my cursors in my stored procedures return the last row of its resultset twice. I was wondering why this is happening. Heres a basic structure of my cursors. Am i doing anything wrong here?

create proc check_no_letter_type as
begin
declare @ltr_type varchar(5)
declare rr_t cursor for
select distinct letter_type from letter_log_rr
open rr_t
create table #ltr_type ( letter_type varchar (5), no_of_letters int)

while (@@sqlstatus !=2)
begin
fetch rr_t into @ltr_type
insert #ltr_type
select
@ltr_type, count(*) from letter_log_rr
where
letter_type = @ltr_type
end
close rr_t
deallocate cursor rr_t
select #ltr_type.letter_type, #ltr_type.no_of_letters from #ltr_type
drop table #ltr_type

end

what it gives is

letter_type no_of_letters
-------------------------------------------
A 10
B 15
C 12
D 22
D 22

As you can see the last row is repeated..

Anybody can help me here?

TIA

JD

 

[grega]

Basically, it's the logic of the while loop which is flawed. When you check a condition in a while loop, you must set that condition just before you check it! Here's your while loop

 while (@@sqlstatus !=2)
 begin
     fetch rr_t into @ltr_type
     insert #ltr_type
     select 
         @ltr_type, count(*) from letter_log_rr
     where
         letter_type = @ltr_type
     end
 close rr_t

Here, you check @@sqlstatus, then fetch the row. In the 1st iteration, all is fine because @@sqlstatus will not be -2 so the loop is entered. When your fetch gets the last row and does the insert, @@sqlstatus will still be OK so you'll go into the loop again. This time, once in the loop, the fetch will set @@sqlstatus = -2, but you're in the loop so the insert will still be done!

One way around this is the following ...

fetch rr_t into @ltr_type
while (@@sqlstatus !=2)
begin
insert #ltr_type
select @ltr_type, count(*)
from letter_log_rr
where letter_type = @ltr_type

fetch rr_t into @ltr_type
end
close rr_t

Here, you're always checking @@sqlstatus just after the fetch.

Greg.

Greg.