Nested Cursors

What is the best way to nest cursors?

This code does not seem to be returning me all of the data.

Code Snippet

DECLARE element_Cursor CURSOR FOR

SELECT ElementTypeRecNo

FROM dbo.tblTemplateElementType

where TemplateRecno = @.TemplateRecNo

OPEN element_cursor

FETCH NEXT FROM Element_Cursor into @.ElementTypeRecno

--delete from tblElementCPO

WHILE @.@.FETCH_STATUS = 0

BEGIN

select @.Count = count (*)

from tblProjTypeSet

where ProjRecno = @.ProjRecNo

if @.Count > 0

begin

select @.ProjTypeRecno = ProjTypeRecno

from tblProjTypeSet

where ProjRecno = @.ProjRecNo

select @.Count = count (*)

FROM dbo.tblElementTypeDep

where TemplateRecno = @.TemplateRecNo

and ProjTypeRecno = @.ProjTypeRecNo

if @.Count > 0

begin

DECLARE ElementTypeDep_Cursor CURSOR FOR

SELECT ElementTypeDepRecNo, PreElementTypeRecNo,

PostElementTypeRecNo, ElapsedTimeDueDates, ElapsedTimePlanDates,

Description

FROM tblElementTypeDep

WHERE (TemplateRecNo = @.TemplateRecNo)

AND (ProjTypeRecNo = @.ProjTypeRecno)

AND (PreElementTypeRecNo = @.ElementTypeRecno)

OPEN ElementTypeDep_cursor

FETCH NEXT FROM ElementTypeDep_Cursor

into @.ElementTypeDepRecno, @.PreElementTypeRecNo,

@.PostElementTypeRecno, @.ElapsedTimeDueDates, @.ElapsedTimePlanDates,

@.Description

WHILE @.@.FETCH_STATUS = 0

BEGIN

select @.PreElementRecNo = ElementRecno

from tblElementCPO

where ProjRecNo = @.ProjRecNo

and IssueRecno = @.IssueRecNo

and ElementTypeRecno = @.PreElementTypeRecno

if @.PreElementRecno is not null

begin

select @.PostElementRecNo = ElementRecno

from tblElementCPO

where ProjRecNo = @.ProjRecNo

and IssueRecno = @.IssueRecNo

and ElementTypeRecno = @.PostElementTypeRecno

if @.PostElementRecno is not null

begin

select @.Count = count (*)

from tblElementDepCPO

where ElementTypeDepRecno = @.ElementTypeDepRecno

and PreElementRecNo = @.PreElementRecNo

and PostElementRecno = @.PostElementRecno

if @.Count = 0

begin

INSERT INTO tblElementDepCPO

(ElementTypeDepRecNo, PreElementRecNo,

PostElementRecNo, ElapsedTimeDueDates,

ElapsedTimePlanDates, Description,

ChangeDate, ChangePerson)

VALUES (@.ElementTypeDepRecno, @.PreElementRecNo,

@.PostElementRecno, @.ElapsedTimeDueDates,

@.ElapsedTimePlanDates, @.Description,

GETDATE(), CURRENT_USER)

end

select @.Count = count (*)

from tblElementAttemptCPO

where ElementRecNo = @.PostElementRecNo

if @.Count = 0

begin

select @.Count = count (*)

from tblElementAttemptCPO

where ElementRecNo = @.PostElementRecNo

if @.Count = 0

begin

select @.NextPlanDate = ProjectedCompletionDate,

@.NextDueDate = RequiredCompletionDate

from tblElementAttemptCPO

where ElementRecno = @.PreElementRecNo

end

else

begin

select @.NextPlanDate = @.StartDate

select @.NextDueDate = @.StartDate

end

select @.NextPlanDate =

dbo.fncAddBusinessDays (@.NextPlanDate, @.ElapsedTimePlanDates)

select @.NextDueDate =

dbo.fncAddBusinessDays (@.NextDueDate, @.ElapsedTimePlanDates)

insert into tblElementAttemptCPO (ElementRecno,

ProjectedCompletionDate, RequiredCompletionDate,

ProjectedStartDate, RequiredStartDate,

ActualStartDate, ActualCompletionDate, AttemptNum,

IsCompleted, IsStarted, ResponsibleRoleTypeRecno,

ChangeDate, ChangePerson)

values (@.PostElementRecno,

@.NextPlanDate, @.NextDueDate,

'1/11/1900', '1/11/1900',

'1/11/1900', '1/11/1900', 0,

0, 0, 0,

GETDATE(), CURRENT_USER)

end

end

end

FETCH NEXT

FROM ElementTypeDep_Cursor

into @.ElementTypeDepRecno, @.PreElementTypeRecNo,

@.PostElementTypeRecno, @.ElapsedTimeDueDates, @.ElapsedTimePlanDates,

@.Description

END

CLOSE elementTypeDep_Cursor

DEALLOCATE elementTypeDep_Cursor

end

FETCH NEXT FROM element_Cursor into @.ElementTypeRecno

END

CLOSE element_Cursor

DEALLOCATE element_Cursor

end

There is a single insert statement hidden within the cursors. Since there is no select statement, there wouldn't be any data returned. Exactly what are you trying to return?

Also, I suggest you post DDL+sample data (i.e. insert statement)+expected output here. We might be able to help draft a non-cursor version.

|||As oj implied, cursors are extremely taxing to a SQL Server and generally should be avoided if possible. Is some cases, it's not possible. But if you'll post the info that oj requested, perhaps this is a case where they can be avoided.

Joe