nested cursor declaration

[digowa]

I want to declare something like:


declare cursor1 cursor for select ...

open cursor1

while @@fetch_status = 0
begin
declare cursor2 cursor for select * from aaa where f1 = <variable_of_cursor_1>

open cursor2

-- checkpoint B
while @@fetch_status = 0
begin
end

close cursor2
deallocate cursor2
end
close cursor1
deallocate cursor1

-----------------------------------------

@@fetch_status at checkpoint B always returns -1

Is it possible to declare such a nested cursor declaration?

 

[JohnDTampaBay]

It is technically possible to nest cursors. However most of us seem to agree that cursors are probably the slowest way to process. If possible, rethink your task and see if processing can be done with set-based queries rather than cursors. Set-based processing will be much quicker and use less resources. --Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[digowa]

I found the error in my statements. It works now ...

 

[Veejc]

What was the error in this code... I am doing something similar, and yes I've analyzed this and I cannot think of another way to perform what I am trying to perform without nesting a cursor.

I am having the exact same problem, can someone tell me how to handle this?

 

[Pattycake245]

What's the error or result you are getting? Show your code or an abbreviated version of it.

If you need to update one row at a time to use to update or compare to the next row then cursors are appropriate. But if you are just performing updates or inserts, where no rows are dependent upon others a set-based solution will perform better.

Tim

 

[Veejc]

Here's some of the code. The Error I am getting only happens when I use

The error is:
Server: Msg 16916, Level 16, State 1, Line 143
A cursor with the name 'member_segments' does not exist.

while @@FEtch_status for the outside loop, the inside loop appears to be fine... any ideas?


DECLARE members_cursor SCROLL CURSOR
FOR select distinct absubno, abpersno
from nhpri_diam_ds01.dbo.tbl_CurrentMemberDimension
where eligibility_flag = 1


OPEN members_cursor


while @@Fetch_status = 0
--while @cursor_cnt < (@row_count + 1)
--set @cursor_cnt= (@cursor_cnt + 1)
--print @cursor_cnt
begin

FETCH next FROM members_cursor into @memberid, @persno

----------------------------------------------------------------------------------------------
--we have one memberid so grab that member's segments
--I've discovered that it's best to return the dates in their original varchar format because
--if I convert at the time of gathering then if there is a null value, this will crash the statement
--because the value is not convertable to a datetime
-- the order of this cursor needs to be ascending on the abeffdt as you see here in order to
-- work correctly, don't change this or you will be looking at the segments upside down
-----------------------------------------------------------------------------------------------

-- START LOOPING through the member_cursor
-- we have the member cursor to loop through but now
-- we need to loop through the member segments, that's
-- what this cursor is for
declare member_segments scroll cursor
for select abeffdt as effdate,
abtermdt as TERMDaTe
from nhpri_diam_ds01.dbo.jmeligm0_dat
where absubno = @memberid and
abpersno = @persno
order by absubno, abpersno, abeffdt asc

open member_segments
fetch next from member_segments into @s_effdate, @s_termdate
-- this will remain the "least date" until we meet the condition of a new effective date, which is dynamic and defined
-- by whatever the user has passed in as number of days acceptable to determine "continuous enrollment"

----------------------------------------------------------------------------------------------------------------------------------
-- THE CODE IN THE FOLLOWING SECTION IS WHERE WE DETERMINE THE 'CONTINUOUS ENROLLMENT --
----------------------------------------------------------------------------------------------------------------------------------
-- We've pulled in another row so now compare that row's values to the previous to determine if we have a new effective date
-- the value in the abtermdt field is never null, it's just blank when there isn't one there, so is not null is not a valid condition


set @least_date = convert(datetime,left(@s_effdate,4) + '/' + substring(@s_effdate,5,2) + '/' + substring(@s_effdate,7,2) )
if (@s_effdate <> '')
begin
set @effdate = convert(datetime,left(@s_effdate,4) + '/' + substring(@s_effdate,5,2) + '/' + substring(@s_effdate,7,2) )
end
if (@s_termdate <> '')
begin
set @termdate = convert(datetime,left(@s_termdate,4) + '/' + substring(@s_termdate,5,2) + '/' + substring(@s_termdate,7,2) )
end
while @@Fetch_status = 0
begin

fetch next from member_segments into @s_next_effdate, @s_next_termdate

if (@s_next_effdate <> '')
begin
set @next_effdate = convert(datetime,left(@s_next_effdate,4) + '/' + substring(@s_next_effdate,5,2) + '/' + substring(@s_next_effdate,7,2) )
end

-- we need to compare the new effective date against the previous termdate before we get the new termdate
if (datediff(day, @termdate, @next_effdate) ) > @days
begin
-- if the condition for the number of days between segments meets what the user passed in
-- then we modify the @least date to hold the new next_effective date as the new least date
set @least_date = @next_effdate
end

if (@s_next_termdate <> '')
begin
set @termdate = convert(datetime,left(@s_next_termdate,4) + '/' + substring(@s_next_termdate,5,2) + '/' + substring(@s_next_termdate,7,2) )
end


end -- this goes with the member_segment loop

insert into #temp_earliest_eff_date (member, persno, memberid, earliest_eff)
values (@memberid, @persno, ltrim(rtrim(@memberid)) + '-' + ltrim(rtrim(@persno)), @least_date)


set @least_date = null
set @s_effdate = ''
set @s_termdate = ''
set @s_next_effdate = ''
set @s_next_termdate = ''
set @termdate = Null
set @effdate = Null
set @next_effdate = null
set @next_termdate = null
close member_segments

end -- this goes with the members_cursor loop
deallocate member_segments
select * from #temp_earliest_eff_date


---- close everything / destroy the cursor
--close member_segments
close members_cursor
deallocate members_cursor
drop table #temp_earliest_eff_date

 

[Veejc]

Hi all

One more question about this, still not getting the correct results using @@fetch_status for nested cursor looping

I have the following

select into my cursor
open cursor
fetch next into myvariables
while
begin
do a bunch of stuff including looping through another cursor
end -- of the insdie cursor
deallocate the INSIDE cursor
close the INSIDE cursor

end -- of the outside begin

The outside cursor only retrieves the FIRST record and then it stops.

No errors, it just only loops one time and then I assume the fetch status is no longer returning zero, perhaps because of the inside fetch_status, but I thought closing and deallocating that inside loop before the end of the outside loop should take care of it... no?

 

[Veejc]

okay i think i missed a few steps

select into cursor
open cursor
fetch next
while
begin
new cursor
open cursor
fetch next
while
begin
end -- of the inside csr
close and deallocated inside cursor
end - of the outside csr

deallocate and close outside csr

any ideas why my outside cursor thinks it's all done?

 

[Pattycake245]

Yes, you're missing a fetch next, actually two of them:

select into cursor
open cursor
fetch next
while
begin
new cursor
open cursor
fetch next
while
begin
end -- of the inside csr
fetch next inside cursor
close and deallocated inside cursor
fetch next outside cursor
end - of the outside csr

deallocate and close outside csr

Tim